Imports System.Data
Imports System.Xml
Imports System.Data.SqlClient
Imports AppsUtility.EncDec
Imports System.Configuration

Public Class GLdataLogic
    Dim util As New AppsUtility.EncDec
    Public strCon As String = util.toDecrypt(System.Configuration.ConfigurationSettings.AppSettings("SQLString"))

    Public Sub GetGLcontrolAccounts(ByRef CashAccount As String, _
                    ByRef KnetAccount As String, ByRef CrCardAccount As String, _
                    ByRef ArAccount As String, ByRef ApAccount As String, _
                    ByRef IcAccount As String, ByRef FaAccount As String, _
                    ByRef SlAccount As String, ByRef CsAccount As String, _
                    ByRef PcAccount As String, ByRef GvAccount As String, _
                    ByRef CrAccount As String, ByRef GvSaleAccount As String)
        CashAccount = ConfigurationManager.AppSettings("GLCashAccount")
        KnetAccount = ConfigurationManager.AppSettings("GLKNetAccount")
        CrCardAccount = ConfigurationManager.AppSettings("GLCardAccount")
        ArAccount = ConfigurationManager.AppSettings("GLReceivableControlAccount")
        ApAccount = ConfigurationManager.AppSettings("GLPayableControlAccount")
        IcAccount = ConfigurationManager.AppSettings("GLInventoryControlAccount")
        FaAccount = ConfigurationManager.AppSettings("GLAssetControlAccount")
        SlAccount = ConfigurationManager.AppSettings("GLSalesControlAccount")
        CsAccount = ConfigurationManager.AppSettings("GLCostControlAccount")
        PcAccount = ConfigurationManager.AppSettings("GLPurchaseControlAccount")
        GvAccount = ConfigurationManager.AppSettings("GLGVAccount")
        CrAccount = ConfigurationManager.AppSettings("GLCRAccount")
        GvSaleAccount = ConfigurationManager.AppSettings("GLGiftVoucherSalesControlAccount")
    End Sub
    Public Function GetCustomers(ByVal WhereClause As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * " & _
                "from Customer " & WhereClause & " order by CusName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("CUSTOMER")
        Try
            da.Fill(ds, "CUSTOMER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetCustomers = ds
    End Function
    Public Function GetProfitLoss(ByVal CompanyID As Integer, ByVal MonthCode As String, ByVal YearCode As Integer, ByVal DptID As Integer, ByVal ShowBudget As Integer, ByVal WhichDpt As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim mcode As Integer
        Select Case MonthCode
            Case "January"
                mcode = 1
            Case "February"
                mcode = 2
            Case "March"
                mcode = 3
            Case "April"
                mcode = 4
            Case "May"
                mcode = 5
            Case "June"
                mcode = 6
            Case "July"
                mcode = 7
            Case "August"
                mcode = 8
            Case "September"
                mcode = 9
            Case "October"
                mcode = 10
            Case "November"
                mcode = 11
            Case "December"
                mcode = 12
        End Select
        strsql = "Exec ProfitLossStmt " & mcode & "," & YearCode & "," & DptID & "," & ShowBudget & "," & WhichDpt
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("PRLO")
        da.SelectCommand.CommandTimeout = 1800
        Try
            da.Fill(ds, "PRLO")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        Dim dRow As DataRow
        For Each dRow In ds.Tables(0).Rows
            If dRow("MTDAMT") < 0 Then
                dRow("MTDAMT") = 0 - dRow("MTDAMT")
            End If
            If dRow("YTDAMT") < 0 Then
                dRow("YTDAMT") = 0 - dRow("YTDAMT")
            End If
            'If dRow("MTDAMT") = 0 Then
            '    dRow("MTDAMT") = System.DBNull.Value
            'End If
            'If dRow("YTDAMT") = 0 Then
            '    dRow("YTDAMT") = System.DBNull.Value
            'End If
        Next
        sqlCon.Close()
        GetProfitLoss = ds
    End Function
    Public Function GetBalanceSheet(ByVal CompanyID As Integer, ByVal MonthCode As String, ByVal YearCode As Integer, ByVal ShowBudget As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim mcode As Integer
        Select Case MonthCode
            Case "January"
                mcode = 1
            Case "February"
                mcode = 2
            Case "March"
                mcode = 3
            Case "April"
                mcode = 4
            Case "May"
                mcode = 5
            Case "June"
                mcode = 6
            Case "July"
                mcode = 7
            Case "August"
                mcode = 8
            Case "September"
                mcode = 9
            Case "October"
                mcode = 10
            Case "November"
                mcode = 11
            Case "December"
                mcode = 12
        End Select
        strsql = "Exec BalanceSheet " & mcode & "," & YearCode & "," & CompanyID & "," & ShowBudget
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("PRLO")
        Try
            da.Fill(ds, "PRLO")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        Dim dRow As DataRow
        For Each dRow In ds.Tables(0).Rows
            If dRow("ACCOUNT") = "0000" Then
                dRow("ACCOUNT") = System.DBNull.Value
            End If
            If dRow("COL1AMT") = 0 Then
                dRow("COL1AMT") = System.DBNull.Value
            Else
                dRow("COL1AMT") = CDbl(dRow("COL1AMT")).ToString("#,##0.000")
            End If
            If IsNumeric(dRow("COL2AMT")) Then
                If dRow("COL2AMT") = 0 Then
                    dRow("COL2AMT") = System.DBNull.Value
                Else
                    dRow("COL2AMT") = CDbl(dRow("COL2AMT")).ToString("#,##0.000")
                End If
            End If
            If IsNumeric(dRow("COL3AMT")) Then
                If dRow("COL3AMT") = 0 Then
                    dRow("COL3AMT") = System.DBNull.Value
                Else
                    dRow("COL3AMT") = CDbl(dRow("COL3AMT")).ToString("#,##0.000")
                End If
            End If
        Next
        sqlCon.Close()
        GetBalanceSheet = ds
    End Function
    Public Function GetProductDetails(ByVal ProductID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from Product where ProductID=" & ProductID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("PRODUCT")
        Try
            da.Fill(ds, "PRODUCT")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetProductDetails = ds
    End Function
    Public Function GetProductDetails2(ByVal ProductCode As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from Product where ProductCode='" & ProductCode & "'"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("PRODUCT")
        Try
            da.Fill(ds, "PRODUCT")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetProductDetails2 = ds
    End Function
    Public Function ProductCodeExist(ByVal CompanyID As Integer, ByVal ProductCode As String) As Boolean
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select count(*) ProdCount from Product where ProductCode='" & ProductCode & "' and CompanyCode=" & CompanyID
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCon.Open()
        Dim ProdCount As Integer
        ProdCount = CInt(sqlCmd.ExecuteScalar)
        sqlCon.Close()
        If ProdCount > 0 Then
            Return True
        Else
            Return False
        End If
    End Function
    Public Function SupplierProductCodeExist(ByVal CompanyID As Integer, ByVal ProductCode As String, ByVal SupID As Integer) As Boolean
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select count(*) ProdCount from Product a inner join ProductSupplier b " & _
        "on a.ProductID=b.ProdID and b.CompanyID=" & CompanyID & " and b.SupID=" & SupplierID & _
        " where a.ProductCode='" & ProductCode & "'"
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCon.Open()
        Dim ProdCount As Integer
        ProdCount = CInt(sqlCmd.ExecuteScalar)
        sqlCon.Close()
        If ProdCount > 0 Then
            Return True
        Else
            Return False
        End If
    End Function
    Public Function SearchProduct(ByVal CompanyID As Integer, ByVal SearchColumn As String, ByVal SearchString As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If SearchColumn.ToUpper <> "PRODUCTBARCODE" Then
            strsql = "SELECT BARID,A.PRODUCTID,B.PRODUCTCODE,A.PRODUCTBARCODE,MANUFPRODUCTCODE," & _
                    "B.SHORTDESCRIPTIONE,B.LONGDESCRIPTIONE,B.FIRSTPRICE " & _
                    "FROM BARCODE A,PRODUCT B " & _
                    "WHERE A.PRODUCTID = B.PRODUCTID AND. B.COMPANYCODE=" & CompanyID & _
                    "AND B." & SearchColumn & " like '" & SearchString & "%' order by B." & SearchColumn
            If SearchColumn.ToUpper = "SHORTDESCRIPTIONE" Then
                strsql = "SELECT BARID,A.PRODUCTID,B.PRODUCTCODE,A.PRODUCTBARCODE,MANUFPRODUCTCODE," & _
                        "B.SHORTDESCRIPTIONE,B.LONGDESCRIPTIONE,B.FIRSTPRICE " & _
                        "FROM BARCODE A,PRODUCT B " & _
                        "WHERE A.PRODUCTID = B.PRODUCTID AND. B.COMPANYCODE=" & CompanyID & _
                        "AND B." & SearchColumn & " like '%" & SearchString & "%' order by B." & SearchColumn
            End If
        Else
            strsql = "SELECT BARID,A.PRODUCTID,B.PRODUCTCODE,A.PRODUCTBARCODE,MANUFPRODUCTCODE," & _
                    "B.SHORTDESCRIPTIONE,B.LONGDESCRIPTIONE,B.FIRSTPRICE " & _
                    "FROM BARCODE A,PRODUCT B " & _
                    "WHERE A.PRODUCTID = B.PRODUCTID AND. B.COMPANYCODE=" & CompanyID & _
                    "AND A." & SearchColumn & " like '" & SearchString & "%' order by A." & SearchColumn
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("PRODUCT")
        Try
            da.Fill(ds, "PRODUCT")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        SearchProduct = ds
    End Function
    Public Function GetCustomerProductDetails(ByVal CompanyID As Integer, ByVal CustomerID As Integer, ByVal ProductCode As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.ProductID,a.ShortDescriptione,a.LongDescriptione,a.ProductCode," & _
                "a.FirstPrice,a.QtyOnHand from Product a inner join CustomerProduct b " & _
                "on a.ProductID=b.ProductID and b.CompanyID=" & CompanyID & " and b.CustomerID=" & CustomerID & _
                " and b.CustomerProdCode='" & ProductCode & "'"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("PRODUCT")
        Try
            da.Fill(ds, "PRODUCT")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetCustomerProductDetails = ds
    End Function
    Public Function GetCustomerProducts(ByVal CompanyID As Integer, ByVal CustomerID As Integer, ByVal WhereClause As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.ProductID,a.ManufProductCode,a.ProductCode," & _
            "b.CustomerProdCode,a.ShortDescriptione,a.FirstPrice " & _
            "from Product a LEFT OUTER join CustomerProduct b on " & _
            "a.ProductID = b.ProductID " & _
            "WHERE b.CompanyID=" & CompanyID & " and b.CustomerID=" & CustomerID & _
            " " & WhereClause & _
            "union all " & _
            "select a.ProductID,a.ManufProductCode,a.ProductCode," & _
            "space(30) CustomerProdCode,a.ShortDescriptione,a.FirstPrice " & _
            "from Product a where " & _
            "a.CompanyCode=" & CompanyID & " " & WhereClause & " and ProductID NOT IN " & _
            "(select a.ProductID from Product a LEFT OUTER join CustomerProduct b on " & _
            "a.ProductID=b.ProductID " & _
            "where b.CompanyID=" & CompanyID & " and b.CustomerID=" & CustomerID & " " & WhereClause & ")"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("PRODUCT")
        Try
            da.Fill(ds, "PRODUCT")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetCustomerProducts = ds
    End Function
    Public Function GetSupplierProducts(ByVal CompanyID As Integer, ByVal SupplierID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.ProductID,a.ManufProductCode,a.ProductCode,a.ShortDescriptione,a.LongDescriptione," & _
                "a.FirstPrice from Product a inner join ProductSupplier b " & _
                "on a.ProductID=b.ProdID and b.CompanyID=" & CompanyID & " and b.SupID=" & SupplierID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("PRODUCT")
        Try
            da.Fill(ds, "PRODUCT")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetSupplierProducts = ds
    End Function
    Public Function SearchCustomerProducts(ByVal CompanyID As Integer, ByVal CustomerID As Integer, ByVal SearchColumn As String, ByVal SearchString As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.ProductID,a.ManufProductCode,a.ProductCode,b.CustomerProdCode,a.ShortDescriptione,a.LongDescriptione," & _
                "a.FirstPrice from Product a inner join CustomerProduct b " & _
                "on a.ProductID=b.ProductID and b.CompanyID=" & CompanyID & " and b.CustomerID=" & CustomerID & _
                " where " & SearchColumn & " like '" & SearchString & "%' order by " & SearchColumn
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("PRODUCT")
        Try
            da.Fill(ds, "PRODUCT")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        SearchCustomerProducts = ds
    End Function
    Public Function SearchSupplierProducts(ByVal CompanyID As Integer, ByVal SupplierID As Integer, ByVal SearchColumn As String, ByVal SearchString As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.ProductID,a.ManufProductCode,a.ProductCode,a.ShortDescriptione,a.LongDescriptione," & _
                "a.FirstPrice from Product a inner join ProductSupplier b " & _
                "on a.ProductID=b.ProdID and b.CompanyID=" & CompanyID & " and b.SupID=" & SupplierID & _
                " where " & SearchColumn & " like '" & SearchString & "%' order by " & SearchColumn
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("PRODUCT")
        Try
            da.Fill(ds, "PRODUCT")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        SearchSupplierProducts = ds
    End Function
    Public Function GetBarCodeDetails(ByVal CompanyID As Integer, ByVal BarCode As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.ProductID,ProductBarCode,b.ProductCode," & _
                    "b.ProductType,b.ShortDescriptionE,b.ShortDescriptionA," & _
                    "b.FirstPrice,b.SecondPrice,b.SaleUnit,b.ActiveFlag," & _
                    "b.OnPromotionFlag,b.KitFlag,b.WhProductFlag " & _
                    "from BarCode a,Product b " & _
                    "where a.ProductBarCode='" & BarCode & "' and a.ProductID=b.ProductID"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("PRODUCT")
        Try
            da.Fill(ds, "PRODUCT")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetBarCodeDetails = ds
    End Function
    Public Function GetBarCodes(ByVal CompanyID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "SELECT BARID,A.PRODUCTID,B.PRODUCTCODE,A.PRODUCTBARCODE," & _
                "B.SHORTDESCRIPTIONE,B.FIRSTPRICE " & _
                "FROM BARCODE A,PRODUCT B " & _
                "WHERE A.PRODUCTID = B.PRODUCTID AND. B.COMPANYCODE=" & CompanyID & _
                " ORDER BY B.SHORTDESCRIPTIONE"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("PRODUCT")
        Try
            da.Fill(ds, "PRODUCT")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetBarCodes = ds
    End Function
    Public Function GetGroupDetails(ByVal GroupID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from AccountGroup where GroupID=" & GroupID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("GROUP")
        Try
            da.Fill(ds, "GROUP")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetGroupDetails = ds
    End Function
    Public Function GetProductBarCodes(ByVal ProductID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from BarCode where ProductID=" & ProductID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("BARCODE")
        Try
            da.Fill(ds, "BARCODE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetProductBarCodes = ds
    End Function
    Public Function GetInvoiceLine(ByVal ProductBarCode As String, ByVal InvNo As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If InvNo.Trim <> "" Then
            strsql = "select * from InvData where InvBarCode='" & ProductBarCode & "' and InvNo='" & InvNo & "'"
        Else
            strsql = "select a.InvNo,c.InvDateTime,b.DepartmentName,InvDesc,InvQty,InvPrice,InvDisp,InvDisa,InvLtot from invdata a, Department b,invhead c " & _
                    "where a.InvID = c.InvID And b.DepartmentID = c.InvShowroomID " & _
                    "and a.InvBarCode='" & ProductBarCode & "' and DateDiff(day,InvDateTime,GetDate())<=30 " & _
                    "order by c.InvDateTime desc"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVLINE")
        Try
            da.Fill(ds, "INVLINE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetInvoiceLine = ds
    End Function
    Public Function GetAccountGroups() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.GroupID,a.GroupName " & _
                    "from AccountGroup a " & _
                    " order by GroupName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ACCOUNTGROUPS")
        Try
            da.Fill(ds, "ACCOUNTGROUPS")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        Dim dRow As DataRow
        dRow = ds.Tables(0).NewRow()
        dRow("GroupID") = 0
        dRow("GroupName") = "ALL GROUPS"
        ds.Tables(0).Rows.Add(dRow)
        sqlCon.Close()
        GetAccountGroups = ds
    End Function
    Public Function GetAccountIdFromNumber(ByVal CompanyID As Integer, ByVal Anumber As String, ByVal Acenter As String) As Integer
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select AccountID " & _
                    "from Account " & _
                    "where AccountNumber='" & Anumber & "' and AccountCenter='" & Acenter & "' "
        sqlCon.Open()
        Dim SqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim Acount As Integer = 0
        Try
            Acount = SqlCmd.ExecuteScalar
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAccountIdFromNumber = Acount
    End Function
    Public Function AccountExist(ByVal CompanyID As Integer, ByVal Anumber As String, ByVal Acenter As String) As Boolean
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select count(*) " & _
                    "from Account " & _
                    "where AccountNumber='" & Anumber & "' and AccountCenter='000' " ' & _
        sqlCon.Open()
        Dim SqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim Acount As Integer = 0
        Try
            Acount = SqlCmd.ExecuteScalar
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        If Acount = 0 Then
            AccountExist = False
        Else
            AccountExist = True
        End If
    End Function
    Public Function BudgetExist(ByVal CompanyID As Integer, ByVal AccountID As Integer, ByVal YearCode As String) As Boolean
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select count(*) " & _
                    "from Budget " & _
                    "where AccountID=" & AccountID & _
                    " and YearCode='" & YearCode & "'"
        sqlCon.Open()
        Dim SqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim Acount As Integer = 0
        Try
            Acount = SqlCmd.ExecuteScalar
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        If Acount = 0 Then
            BudgetExist = False
        Else
            BudgetExist = True
        End If
    End Function

    Public Function InsertAccountDetails(ByVal CompanyID As Integer, ByVal GroupID As Integer, ByVal AccountName As String, _
               ByVal AccountNumber As String, ByVal AccountDpt As String) As String
        Dim Response As String
        If Not AccountExist(CompanyID, AccountNumber, AccountDpt) Then
            Dim sqlCon As SqlConnection = New SqlConnection(strCon)
            Dim strsql As String
            sqlCon.Open()
            strsql = "Insert into Account(CompanyID,AccountGroupID,AccountName,AccountNumber,AccountCenter) " & _
                    "values(" & CompanyID & "," & GroupID & "," & _
                    "'" & AccountName & "','" & AccountNumber & "','" & AccountDpt & "')"
            Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
            Try
                sqlCmd.ExecuteNonQuery()
                Response = "OK"
            Catch ex As Exception
                Response = "ERROR"
            End Try
            sqlCon.Close()
        Else
            Response = "EXIST"
        End If
        InsertAccountDetails = Response
    End Function
    Public Function InsertBudgetDetails(ByVal CompanyID As Integer, ByVal YearCode As String, _
                ByVal AccountNumber As String, ByVal AccountDpt As String, _
                ByVal Jan As String, ByVal Feb As String, ByVal Mar As String, _
                ByVal Apr As String, ByVal May As String, ByVal Jun As String, _
                ByVal Jul As String, ByVal Aug As String, ByVal Sep As String, _
                ByVal Oct As String, ByVal Nov As String, ByVal Dec As String) As String
        Dim AccountID As Integer = 0
        AccountID = GetAccountIdFromNumber(CompanyID, AccountNumber, AccountDpt)
        Dim Response As String
        If Not BudgetExist(CompanyID, AccountID, YearCode) Then
            Jan = PrefixInteger(Jan)
            Feb = PrefixInteger(Feb)
            Mar = PrefixInteger(Mar)
            Apr = PrefixInteger(Apr)
            May = PrefixInteger(May)
            Jun = PrefixInteger(Jun)
            Jul = PrefixInteger(Jul)
            Aug = PrefixInteger(Aug)
            Sep = PrefixInteger(Sep)
            Oct = PrefixInteger(Oct)
            Nov = PrefixInteger(Nov)
            Dec = PrefixInteger(Dec)
            Dim sqlCon As SqlConnection = New SqlConnection(strCon)
            Dim strsql As String
            sqlCon.Open()
            strsql = "Insert into Budget(CompanyID,AccountID,YearCode," & _
                    "JanAmount,FebAmount,MarAmount,AprAmount,MayAmount,JunAmount," & _
                    "JulAmount,AugAmount,SepAmount,OctAmount,NovAmount,DecAmount) " & _
                    "values(" & CompanyID & "," & AccountID & "," & _
                    "'" & YearCode & "','" & Jan & "','" & Feb & "','" & Mar & "'," & _
                    "'" & Apr & "','" & May & "','" & Jun & "','" & Jul & "'," & _
                    "'" & Aug & "','" & Sep & "','" & Oct & "','" & Nov & "','" & Dec & "')"
            Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
            Try
                sqlCmd.ExecuteNonQuery()
                Response = "OK"
            Catch ex As Exception
                Response = "ERROR"
            End Try
            sqlCon.Close()
        Else
            Response = "EXIST"
        End If
        InsertBudgetDetails = Response
    End Function
    Public Function UpdateAccountDetails(ByVal CompanyID As Integer, ByVal GroupID As Integer, ByVal AccountName As String, _
                ByVal AccountNumber As String, ByVal AccountDpt As String) As String
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Update Account " & _
                "set AccountGroupID=" & GroupID & ",AccountName='" & AccountName & "' " & _
                    "where AccountNumber='" & AccountNumber & "' and AccountCenter='" & AccountDpt & "' "
        '"and CompanyID=" & CompanyID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim Response As String
        Try
            sqlCmd.ExecuteNonQuery()
            Response = "OK"
        Catch ex As Exception
            Response = "ERROR"
        End Try
        sqlCon.Close()
        UpdateAccountDetails = Response
    End Function
    Public Function UpdateBudgetDetails(ByVal CompanyID As Integer, ByVal BudgetID As Integer, _
                ByVal Jan As String, ByVal Feb As String, ByVal Mar As String, _
                ByVal Apr As String, ByVal May As String, ByVal Jun As String, _
                ByVal Jul As String, ByVal Aug As String, ByVal Sep As String, _
                ByVal Oct As String, ByVal Nov As String, ByVal Dec As String) As String
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Jan = PrefixInteger(Jan)
        Feb = PrefixInteger(Feb)
        Mar = PrefixInteger(Mar)
        Apr = PrefixInteger(Apr)
        May = PrefixInteger(May)
        Jun = PrefixInteger(Jun)
        Jul = PrefixInteger(Jul)
        Aug = PrefixInteger(Aug)
        Sep = PrefixInteger(Sep)
        Oct = PrefixInteger(Oct)
        Nov = PrefixInteger(Nov)
        Dec = PrefixInteger(Dec)
        strsql = "Update Budget " & _
                "set JanAmount='" & Jan & "',FebAmount='" & Feb & "',MarAmount='" & Mar & "'," & _
                    "AprAmount='" & Apr & "',MayAmount='" & May & "',JunAmount='" & Jun & "'," & _
                    "JulAmount='" & Jul & "',AugAmount='" & Aug & "',SepAmount='" & Sep & "'," & _
                    "OctAmount='" & Oct & "',NovAmount='" & Nov & "',DecAmount='" & Dec & "' " & _
                    "where BudID=" & BudgetID & " and CompanyID = " & CompanyID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim Response As String
        Try
            sqlCmd.ExecuteNonQuery()
            Response = "OK"
        Catch ex As Exception
            Response = "ERROR"
        End Try
        sqlCon.Close()
        UpdateBudgetDetails = Response
    End Function
    Public Function GetAccountGroupDetails(ByVal GroupID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.* " & _
                    "from AccountGroup a " & _
                    "where a.GroupID=" & GroupID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ACCOUNTGROUPS")
        Try
            da.Fill(ds, "ACCOUNTGROUPS")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAccountGroupDetails = ds
    End Function
    Public Function GetAccountDetails(ByVal CompanyID As Integer, ByVal MainAccount As String, ByVal SubAccount As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        'strsql = "select a.*,b.* from Account a,AccountGroup b " & _
        '            "where a.CompanyID=" & CompanyID & " and a.AccountNumber='" & MainAccount & "' " & _
        '            "and AccountCenter='" & SubAccount & "' and a.AccountGroupID=b.GroupID"
        strsql = "select a.*,b.* from Account a,AccountGroup b " & _
                    "where a.AccountNumber='" & MainAccount & "' " & _
                    "and AccountCenter='000' and a.AccountGroupID=b.GroupID and AccountNumber<>'0000'"

        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ACCOUNT")
        Try
            da.Fill(ds, "ACCOUNT")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAccountDetails = ds
    End Function
    Public Function GetBudgetDetails(ByVal CompanyID As Integer, ByVal BudgetID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.*,b.* from Budget a,Account b " & _
                    "where a.BudID=" & BudgetID & " " & _
                    "and a.AccountID=b.AccountID"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("BUDGET")
        Try
            da.Fill(ds, "BUDGET")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetBudgetDetails = ds
    End Function
    Public Function GetAccountDetailsByAccountID(ByVal AccountID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.* from Account a " & _
                    "where a.AccountID=" & AccountID & ""
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ACCOUNT")
        Try
            da.Fill(ds, "ACCOUNT")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAccountDetailsByAccountID = ds
    End Function
    Public Function GetCompanyDepartments(ByVal CompanyID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.AccountID,a.AccountName " & _
                    "from Account a where a.AccountNumber='0000' " & _
                    "order by AccountNumber "
        '"order by AccountName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ACCOUNTGROUPS")
        Try
            da.Fill(ds, "ACCOUNTGROUPS")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        Dim dRow As DataRow
        dRow = ds.Tables(0).NewRow()
        dRow("AccountID") = 0
        dRow("AccountName") = "ALL DEPARTMENTS"
        ds.Tables(0).Rows.Add(dRow)
        sqlCon.Close()
        GetCompanyDepartments = ds
    End Function
    Public Function GetProfitCenter(ByVal AccountID As Integer) As String
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select AccountCenter " & _
                    "from Account where AccountID=" & AccountID
        sqlCon.Open()
        Dim SqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim Center As String = "000"
        Try
            Center = SqlCmd.ExecuteScalar
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetProfitCenter = Center
    End Function
    Public Function GetAllCompany() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select CompanyID,CompanyName from Company " & _
                 "order by CompanyName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAllCompany = ds
    End Function
    Public Function GetProfitCenterCode(ByVal DepartmentID As Integer) As String
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select GLcenterCode from Department where DepartmentID=" & DepartmentID
        sqlCon.Open()
        Dim SqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim pc As String = "000"
        Try
            pc = SqlCmd.ExecuteScalar
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        Return pc
    End Function
    Public Function GetCompanyCrLimitPrompt() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select CompanyID,CompanyName,0 CreditLimit,0 ValidCompany from Company " & _
                 "order by CompanyName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("COMPANY")
        Try
            da.Fill(ds, "COMPANY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetCompanyCrLimitPrompt = ds
    End Function
    Public Function GetCompanyShowrooms(ByVal CompanyID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select DepartmentID,DepartmentName from Department " & _
                 "where CompanyID=" & CompanyID & " and ShowroomFlag=1 order by DepartmentName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SHOWROOM")
        Try
            da.Fill(ds, "SHOWROOM")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        Dim dRow As DataRow
        dRow = ds.Tables(0).NewRow()
        dRow("DepartmentID") = 0
        dRow("DepartmentName") = "ALL SHOWROOMS"
        ds.Tables(0).Rows.Add(dRow)
        sqlCon.Close()
        GetCompanyShowrooms = ds
    End Function
    Public Function GetCompanySalesmen(ByVal CompanyID As Integer, ByVal DepartmentID As Integer, Optional ByVal ShowAll As Integer = 1) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select SalesmanID,SalesmanName from Salesman " & _
                 "where DepartmentID=" & DepartmentID & " and CompanyID=" & CompanyID & " order by SalesmanName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SALESMAN")
        Try
            da.Fill(ds, "SALESMAN")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        If ShowAll = 1 Then
            Dim dRow As DataRow
            dRow = ds.Tables(0).NewRow()
            dRow("SalesmanID") = 0
            dRow("SalesmanName") = "ALL SALESMEN"
            ds.Tables(0).Rows.Add(dRow)
        End If
        sqlCon.Close()
        GetCompanySalesmen = ds
    End Function
    Public Function GetShowroomsRol(ByVal CompanyID As Integer, ByVal ProdID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.DepartmentID,a.DepartmentName,isnull(b.MinimumQty,0) MinimumQty," & _
                 "isnull(b.PresentQty,0) PresentQty from Department a " & _
                "left outer join ShowroomRol b on a.DepartmentID=b.ShowroomID " & _
                "where a.CompanyID=" & CompanyID & " and a.ShowroomFlag = 1 and b.ProductID=" & ProdID & _
                " union all " & _
                "select a.DepartmentID,a.DepartmentName,0 MinimumQty,0 PresentQty " & _
                "from Department a where a.CompanyID=" & CompanyID & " and a.ShowroomFlag=1 " & _
                "and a.DepartmentID not in " & _
                "(select a.DepartmentID from Department a " & _
                "left outer join ShowroomRol b on a.DepartmentID=b.ShowroomID " & _
                "where a.CompanyID=" & CompanyID & " and a.ShowroomFlag = 1 and b.ProductID=" & ProdID & ") " & _
                "order by a.DepartmentName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SHOWROOM")
        Try
            da.Fill(ds, "SHOWROOM")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetShowroomsRol = ds
    End Function
    Public Function GetProductSupplierPrompt(ByVal CompanyID As Integer, ByVal ProdID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.SupID,a.SupName," & _
                "isnull(b.ValidSup,0) ValidSup " & _
                "from Supplier a left outer join ProductSupplier b " & _
                "on a.SupID=b.SupID where a.CompanyID=" & CompanyID & " and  " & _
                "b.ProdID=" & ProdID & " " & _
                "union all " & _
                "select a.SupID,a.SupName,0 ValidSup " & _
                "from Supplier a where a.CompanyID=3 " & _
                "and a.SupID not in " & _
                "(select a.SupID from Supplier a left outer join ProductSupplier b " & _
                "on a.SupID=b.SupID where a.CompanyID=" & CompanyID & " and  " & _
                "b.ProdID=" & ProdID & ") " & _
                "order by a.SupName"

        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SUPPLIER")
        Try
            da.Fill(ds, "SUPPLIER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetProductSupplierPrompt = ds
    End Function
    Public Function GetCompanySuppliers(ByVal CompanyID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.SupID,a.SupName " & _
                "from Supplier a,CompanySupplier b " & _
                "where b.CompanyID=" & CompanyID & " and a.SupID=b.SupID " & _
                "order by a.SupName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SUPPLIER")
        Try
            da.Fill(ds, "SUPPLIER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetCompanySuppliers = ds
    End Function
    Public Function GetDocumentTypes(ByVal CompanyID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from FasDoc order by Doc_Name"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("DOCUMENT")
        Try
            da.Fill(ds, "DOCUMENT")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetDocumentTypes = ds
    End Function
    Public Function GetCompanyCustomers(ByVal CompanyID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.CusID,a.CusName,a.CusAdr1,a.CusAdr2,a.CusDAdr1,a.CusDAdr2 " & _
                "from Customer a,CompanyCustomer b " & _
                "where b.CompanyID=" & CompanyID & " and a.CusID=b.CusID " & _
                "order by a.CusName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("CUSTOMER")
        Try
            da.Fill(ds, "CUSTOMER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetCompanyCustomers = ds
    End Function
    Public Function GetAccountsByCompany(ByVal CompanyID As Integer, ByVal GroupID As Integer, ByVal DepartmentID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        'If DepartmentID = 0 Then
        If GroupID = 0 Then
            strsql = "select a.AccountID,a.AccountNumber,a.AccountCenter,a.AccountName,b.GroupName " & _
                    "from Account a,AccountGroup b " & _
                    "where a.AccountGroupID=b.GroupID " & _
                    "order by a.AccountName"
        Else
            strsql = "select a.AccountID,a.AccountNumber,a.AccountCenter,a.AccountName,b.GroupName " & _
                    "from Account a,AccountGroup b " & _
                    "where a.AccountGroupID=" & GroupID & " " & _
                    "and a.AccountGroupID=b.GroupID " & _
                    "order by a.AccountName"
        End If
        'Else
        '    If GroupID = 0 Then
        '        strsql = "select a.AccountID,a.AccountNumber,a.AccountCenter,a.AccountName,b.GroupName " & _
        '                "from Account a,AccountGroup b " & _
        '                "where a.CompanyID=" & CompanyID & "  " & _
        '                "and a.AccountGroupID=b.GroupID " & _
        '                "and a.AccountCenter=" & _
        '                "(select AccountCenter from Account " & _
        '                "where AccountID=" & DepartmentID & ") " & _
        '                "order by a.AccountName"
        '    Else
        '        strsql = "select a.AccountID,a.AccountNumber,a.AccountCenter,a.AccountName,b.GroupName " & _
        '                "from Account a,AccountGroup b " & _
        '                "where a.CompanyID=" & CompanyID & "  " & _
        '                "and a.AccountGroupID=" & GroupID & " " & _
        '                "and a.AccountGroupID=b.GroupID " & _
        '                "and a.AccountCenter=" & _
        '                "(select AccountCenter from Account " & _
        '                "where AccountID=" & DepartmentID & ") " & _
        '                "order by a.AccountName"
        '    End If
        'End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ACCOUNT")
        Try
            da.Fill(ds, "ACCOUNT")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAccountsByCompany = ds
    End Function
    Public Function GetAllAccounts(ByVal GroupID As Integer, ByVal DepartmentID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If DepartmentID = 0 Then
            If GroupID = 0 Then
                strsql = "select a.*,b.GroupName " & _
                        "from Account a,AccountGroup b " & _
                        "where a.AccountGroupID=b.GroupID and a.AccountNumber<>'0000' " & _
                        "order by a.AccountName"
            Else
                strsql = "select a.*,b.GroupName " & _
                        "from Account a,AccountGroup b " & _
                        "where a.AccountGroupID=" & GroupID & " " & _
                        "and a.AccountGroupID=b.GroupID and a.AccountNumber<>'0000' " & _
                        "order by a.AccountName"
            End If
        Else
            If GroupID = 0 Then
                strsql = "select a.*,b.GroupName " & _
                        "from Account a,AccountGroup b " & _
                        "where a.AccountGroupID=b.GroupID " & _
                        "and a.AccountCenter=" & _
                        "(select AccountCenter from Account " & _
                        "where AccountID=" & DepartmentID & ") " & _
                        "order by a.AccountName"
            Else
                strsql = "select a.*,b.GroupName " & _
                        "from Account a,AccountGroup b " & _
                        "where a.AccountGroupID=" & GroupID & " " & _
                        "and a.AccountGroupID=b.GroupID " & _
                        "and a.AccountCenter=" & _
                        "(select AccountCenter from Account " & _
                        "where AccountID=" & DepartmentID & ") " & _
                        "order by a.AccountName"
            End If
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ACCOUNT")
        Try
            da.Fill(ds, "ACCOUNT")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAllAccounts = ds
    End Function
    Public Function GetBudgetsByCompany(ByVal CompanyID As Integer, ByVal YearCode As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.*,b.AccountNumber,b.AccountCenter,b.AccountName, " & _
                "JanAmount+FebAmount+MarAmount+AprAmount+MayAmount+JunAmount+ " & _
                "JulAmount+AugAmount+SepAmount+OctAmount+NovAmount+DecAmount TotalAmount " & _
                "from Account b left outer join Budget a " & _
                "on a.AccountID=b.AccountID " & _
                "where a.YearCode='" & YearCode & "' " & _
                "order by b.AccountName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ACCOUNT")
        Try
            da.Fill(ds, "ACCOUNT")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetBudgetsByCompany = ds
    End Function
    Public Function GetCompanyWarehouses(ByVal CompanyID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from Warehouse " & _
                "where CompanyID=" & CompanyID & _
                " order by WarehouseName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("WAREHOUSE")
        Try
            da.Fill(ds, "WAREHOUSE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetCompanyWarehouses = ds
    End Function
    Public Function GetCustomerDetails(ByVal CustomerID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.* from Customer a " & _
                "where a.CusID=" & CustomerID
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("CUSTOMER")
        Try
            da.Fill(ds, "CUSTOMER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetCustomerDetails = ds
    End Function
    Public Function GetCurrencies() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select CurrencyID,CurrencyCode,CurrencyName from Currency " & _
                "order by CurrencyName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("CURRENCY")
        Try
            da.Fill(ds, "CURRENCY")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetCurrencies = ds
    End Function
    Public Function GetShowroomsRolPrompt(ByVal CompanyID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.DepartmentID,a.DepartmentName," & _
                "0 MinimumQty " & _
                "from Department a " & _
                "where a.CompanyID=" & CompanyID & " and a.ShowroomFlag = 1 " & _
                "order by a.DepartmentName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SHOWROOM")
        Try
            da.Fill(ds, "SHOWROOM")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetShowroomsRolPrompt = ds
    End Function
    Public Function GetSuppliersProdPrompt(ByVal CompanyID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.SupID,a.SupName," & _
                "0 ValidSup " & _
                "from Supplier a " & _
                "where a.CompanyID=" & CompanyID & _
                " order by a.SupName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SUPPLIER")
        Try
            da.Fill(ds, "SUPPLIER")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetSuppliersProdPrompt = ds
    End Function
    Public Function GetBrandCode(ByVal BrandID As Integer) As String
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select GroupCode from ProductGroup where GroupID=" & BrandID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim BrandCode As String
        Try
            BrandCode = sqlCmd.ExecuteScalar
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetBrandCode = BrandCode
    End Function

    Public Function GetAllGroups() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select a.* " & _
                "from AccountGroup a  " & _
                " order by GroupName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("GROUP")
        Try
            da.Fill(ds, "GROUP")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAllGroups = ds
    End Function
    Public Function GetAllEvents() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from SaleEvents order by EventName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("EVENTS")
        Try
            da.Fill(ds, "EVENTS")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAllEvents = ds
    End Function
    Public Function GetAllPosRequest(ByVal ShowroomID As Integer, ByVal SalesmanID As Integer, ByVal Status As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If SalesmanID = 0 And ShowroomID = 0 Then
            If Status = 0 Then
                strsql = "Select a.*,b.CusName,d.DepartmentName,c.SalesmanName " & _
                        "from PosCusRequest a,PosCustomer b,Salesman c,Department d " & _
                        "where a.ReqProfileID = b.ProfileID and a.ReqShowroomID=d.DepartmentID " & _
                        "and a.ReqSalesmanID=c.SalesmanID order by ReqDateTime desc"
            Else
                strsql = "Select a.*,b.CusName,d.DepartmentName,c.SalesmanName " & _
                        "from PosCusRequest a,PosCustomer b,Salesman c,Department d " & _
                        "where a.ReqProfileID = b.ProfileID and a.ReqShowroomID=d.DepartmentID " & _
                        "and a.ReqSalesmanID=c.SalesmanID and ltrim(rtrim(ReqStatus))='PENDING' order by ReqDateTime desc"
            End If
        Else
            If SalesmanID > 0 Then
                If Status = 0 Then
                    strsql = "Select a.*,b.CusName,d.DepartmentName,c.SalesmanName " & _
                            "from PosCusRequest a,PosCustomer b,Salesman c,Department d " & _
                            "where a.ReqProfileID = b.ProfileID and a.ReqShowroomID=d.DepartmentID " & _
                            "and a.ReqSalesmanID=c.SalesmanID and ReqSalesmanID=" & SalesmanID & " order by ReqDateTime desc"
                Else
                    strsql = "Select a.*,b.CusName,d.DepartmentName,c.SalesmanName " & _
                            "from PosCusRequest a,PosCustomer b,Salesman c,Department d " & _
                            "where a.ReqProfileID = b.ProfileID and a.ReqShowroomID=d.DepartmentID " & _
                            "and a.ReqSalesmanID=c.SalesmanID and ReqSalesmanID=" & SalesmanID & " and ltrim(rtrim(ReqStatus))='PENDING' order by ReqDateTime desc"
                End If
            End If
            If ShowroomID > 0 Then
                If Status = 0 Then
                    strsql = "Select a.*,b.CusName,d.DepartmentName,c.SalesmanName " & _
                            "from PosCusRequest a,PosCustomer b,Salesman c,Department d " & _
                            "where a.ReqProfileID = b.ProfileID and a.ReqShowroomID=d.DepartmentID " & _
                            "and a.ReqSalesmanID=c.SalesmanID and ReqShowroomID=" & ShowroomID & " order by ReqDateTime desc"
                Else
                    strsql = "Select a.*,b.CusName,d.DepartmentName,c.SalesmanName " & _
                            "from PosCusRequest a,PosCustomer b,Salesman c,Department d " & _
                            "where a.ReqProfileID = b.ProfileID and a.ReqShowroomID=d.DepartmentID " & _
                            "and a.ReqSalesmanID=c.SalesmanID and ReqShowroomID=" & ShowroomID & " and ltrim(rtrim(ReqStatus))='PENDING' order by ReqDateTime desc"
                End If
            End If
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("REQUEST")
        Try
            da.Fill(ds, "REQUEST")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetAllPosRequest = ds
    End Function
    Public Function GetGroupLevels() As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select LevelID,LevelName from GroupLevel " & _
                 "order by LevelName"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("LEVEL")
        Try
            da.Fill(ds, "LEVEL")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        Dim dRow As DataRow
        dRow = ds.Tables(0).NewRow()
        dRow("LevelID") = 0
        dRow("LevelName") = "ALL LEVELS"
        ds.Tables(0).Rows.Add(dRow)
        sqlCon.Close()
        GetGroupLevels = ds
    End Function
    Public Function GetSavedInvoiceNumbers(ByVal ShowroomID As Integer, ByVal CashierID As Integer, ByVal InvStatus As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If InvStatus = "SAVE" Then
            strsql = "select InvID,InvNO from InvHead " & _
                     "where InvShowroomID=" & ShowroomID & " and InvCashierID=" & CashierID & " and InvStatus='" & InvStatus & "'"
        Else
            strsql = "select InvID,InvNO from InvHead " & _
                     "where InvStatus='" & InvStatus & "'"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVOICE")
        Try
            da.Fill(ds, "INVOICE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetSavedInvoiceNumbers = ds
    End Function
    Public Function GetSavedInvoiceHeader(ByVal ShowroomID As Integer, ByVal CashierID As Integer, ByVal InvStatus As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from InvHead " & _
                 "where InvShowroomID=" & ShowroomID & " and InvCashierID=" & CashierID & " and InvStatus='" & InvStatus & "' order by InvDateTime desc"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVOICE")
        Try
            da.Fill(ds, "INVOICE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetSavedInvoiceHeader = ds
    End Function
    Public Function GetSavedPurhRtnHeader(ByVal RtnID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from PurchaseRtnHead " & _
                 "where PrID=" & RtnID & " order by prid desc"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVOICE")
        Try
            da.Fill(ds, "INVOICE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetSavedPurhRtnHeader = ds
    End Function
    Public Function GetWholesaleInvoiceHeaderByStatus(ByVal DepartmentID As Integer, ByVal UserID As Integer, ByVal InvStatus As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from InvwHead " & _
                 "where InvDepartmentID=" & DepartmentID & " and InvUserID=" & UserID & " and InvStatus='" & InvStatus & "' order by InvID desc"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVOICE")
        Try
            da.Fill(ds, "INVOICE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetWholesaleInvoiceHeaderByStatus = ds
    End Function
    Public Function GetWholesaleReturnHeaderByStatus(ByVal DepartmentID As Integer, ByVal UserID As Integer, ByVal InvStatus As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from RtnwHead " & _
                 "where InvDepartmentID=" & DepartmentID & " and InvUserID=" & UserID & " and InvStatus='" & InvStatus & "' order by RtnID desc"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVOICE")
        Try
            da.Fill(ds, "INVOICE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetWholesaleReturnHeaderByStatus = ds
    End Function

    Public Function GetWholesaleQuotationHeaderByStatus(ByVal DepartmentID As Integer, ByVal UserID As Integer, ByVal InvStatus As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from QtnHead " & _
                 "where InvDepartmentID=" & DepartmentID & " and InvUserID=" & UserID & " and InvStatus='" & InvStatus & "' order by QtnID desc"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVOICE")
        Try
            da.Fill(ds, "INVOICE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetWholesaleQuotationHeaderByStatus = ds
    End Function
    Public Function GetWholesaleLpoHeaderByStatus(ByVal DepartmentID As Integer, ByVal UserID As Integer, ByVal InvStatus As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from LpoHead " & _
                 "where InvDepartmentID=" & DepartmentID & " and InvUserID=" & UserID & " and InvStatus='" & InvStatus & "' order by LpoID desc"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVOICE")
        Try
            da.Fill(ds, "INVOICE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetWholesaleLpoHeaderByStatus = ds
    End Function
    Public Function GetReturnHeaderByStatus(ByVal CompanyID As Integer, ByVal InvStatus As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from PurchaseRtnHead " & _
                 "where PrCompanyID=" & CompanyID & " and  PrStatus='" & InvStatus & "' order by PrID desc"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVOICE")
        Try
            da.Fill(ds, "INVOICE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetReturnHeaderByStatus = ds
    End Function
    Public Function GetOrderHeader(ByVal CompanyID As Integer, ByVal Status As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If Status = "ALL" Then
            strsql = "select a.*,b.SupName from PurchaseOrderHead a,Supplier b " & _
                     "where a.PoSupID=b.SupID and a.PoCompanyID=" & CompanyID & " order by PoDate desc"
        Else
            strsql = "select a.*,b.SupName from PurchaseOrderHead a,Supplier b " & _
                     "where a.PoStatus='" & Status & "' and a.PoSupID=b.SupID and a.PoCompanyID=" & CompanyID & " order by PoDate desc"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ORDRHEAD")
        Try
            da.Fill(ds, "ORDRHEAD")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetOrderHeader = ds
    End Function
    Public Function GetPendingOrderHeaderList(ByVal CompanyID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select PoID,PoNumber,PoDate from PurchaseOrderHead  " & _
                 "where PoStatus='PENDING' and PoCompanyID=" & CompanyID & " order by PoDate desc"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ORDRHEAD")
        Try
            da.Fill(ds, "ORDRHEAD")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetPendingOrderHeaderList = ds
    End Function
    Public Function GetShipmentList(ByVal CompanyID As Integer, ByVal Status As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select ShID,ShNumber,ShDate,ShRemark from ShipmentHead  " & _
                 "where ShStatus='" & Status & "' and ShCompanyID=" & CompanyID & " order by ShNumber desc"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SHIPHEAD")
        Try
            da.Fill(ds, "SHIPHEAD")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetShipmentList = ds
    End Function
    Public Function GetInvoiceList(ByVal ShowroomID As Integer, ByVal CashierID As Integer, ByVal RptDate As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim RpDate As Date
        Try
            RpDate = Date.Parse(RptDate)
        Catch ex As Exception
            Err.Raise(666, "GetInvoiceList", "Invalid Date")
            Exit Function
        End Try
        strsql = "select InvID,InvNo,InvDateTime,InvStatus,InvGrossAmt,InvGdp,InvGda,InvNet from InvHead " & _
                     "where InvShowroomID=" & ShowroomID & " and InvCashierID=" & CashierID & _
                    " and day(InvDateTime)=" & RpDate.Day & " and month(InvDateTime)=" & RpDate.Month & _
                    " and year(InvDateTime)=" & RpDate.Year & " order by InvDateTime desc"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVOICE")
        Try
            da.Fill(ds, "INVOICE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetInvoiceList = ds
    End Function
    Public Function GetInvoiceListTotal(ByVal ShowroomID As Integer, ByVal CashierID As Integer, ByVal RptDate As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim RpDate As Date
        Try
            RpDate = Date.Parse(RptDate)
        Catch ex As Exception
            Err.Raise(666, "GetInvoiceListTotal", "Invalid Date")
            Exit Function
        End Try
        strsql = "select sum(InvGrossAmt) GrossAmt,sum(InvGda) DisAmt,sum(InvNet) NetAmt from InvHead " & _
                     "where InvShowroomID=" & ShowroomID & " and InvCashierID=" & CashierID & _
                    " and day(InvDateTime)=" & RpDate.Day & " and month(InvDateTime)=" & RpDate.Month & _
                    " and year(InvDateTime)=" & RpDate.Year & " group by day(InvDateTime)"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVOICE")
        Try
            da.Fill(ds, "INVOICE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetInvoiceListTotal = ds
    End Function
    Public Function GetWholesaleInvoiceHeader(ByVal InvoiceID As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If IsNumeric(Left(InvoiceID, 1)) Then
            strsql = "select * from InvWHead " & _
                     "where InvID='" & InvoiceID & "'"
        Else
            strsql = "select * from InvWHead " & _
                     "where InvNo='" & InvoiceID & "'"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVHEAD")
        Try
            da.Fill(ds, "INVHEAD")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetWholesaleInvoiceHeader = ds
    End Function
    Public Function GetWholesaleQuotationHeader(ByVal InvoiceID As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If IsNumeric(Left(InvoiceID, 1)) Then
            strsql = "select * from QtnHead " & _
                     "where QtnID='" & InvoiceID & "'"
        Else
            strsql = "select * from QtnHead " & _
                     "where QtnNo='" & InvoiceID & "'"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVHEAD")
        Try
            da.Fill(ds, "INVHEAD")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetWholesaleQuotationHeader = ds
    End Function
    Public Function GetWholesaleReturnHeader(ByVal InvoiceID As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If IsNumeric(Left(InvoiceID, 1)) Then
            strsql = "select * from RtnWHead " & _
                     "where RtnID='" & InvoiceID & "'"
        Else
            strsql = "select * from RtnWHead " & _
                     "where RtnNo='" & InvoiceID & "'"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("RTNHEAD")
        Try
            da.Fill(ds, "RTNHEAD")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetWholesaleReturnHeader = ds
    End Function
    Public Function GetWholesaleLpoHeader(ByVal InvoiceID As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If IsNumeric(Left(InvoiceID, 1)) Then
            strsql = "select * from LpoHead " & _
                     "where LpoID='" & InvoiceID & "'"
        Else
            strsql = "select * from LpoHead " & _
                     "where LpoNo='" & InvoiceID & "'"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVHEAD")
        Try
            da.Fill(ds, "INVHEAD")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetWholesaleLpoHeader = ds
    End Function
    Public Function GetInvoiceDetails(ByVal InvoiceID As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If IsNumeric(Left(InvoiceID, 1)) Then
            strsql = "select * from InvData " & _
                     "where InvID='" & InvoiceID & "'"
        Else
            strsql = "select * from InvData " & _
                     "where InvNo='" & InvoiceID & "'"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVDATA")
        Try
            da.Fill(ds, "INVDATA")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetInvoiceDetails = ds
    End Function
    Public Function GetWholesaleInvoiceDetails(ByVal InvoiceID As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If IsNumeric(Left(InvoiceID, 1)) Then
            strsql = "select * from InvWData " & _
                     "where InvID='" & InvoiceID & "'"
        Else
            strsql = "select * from InvWData " & _
                     "where InvNo='" & InvoiceID & "'"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVDATA")
        Try
            da.Fill(ds, "INVDATA")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetWholesaleInvoiceDetails = ds
    End Function

    Public Function GetWholesaleQuotationDetails(ByVal InvoiceID As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If IsNumeric(Left(InvoiceID, 1)) Then
            strsql = "select * from QtnData " & _
                     "where QtnID='" & InvoiceID & "'"
        Else
            strsql = "select * from QtnData " & _
                     "where QtnNo='" & InvoiceID & "'"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVDATA")
        Try
            da.Fill(ds, "INVDATA")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetWholesaleQuotationDetails = ds
    End Function
    Public Function GetWholesaleReturnDetails(ByVal InvoiceID As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If IsNumeric(Left(InvoiceID, 1)) Then
            strsql = "select * from RtnWData " & _
                     "where RtnID='" & InvoiceID & "'"
        Else
            strsql = "select * from RtnWData " & _
                     "where RtnNo='" & InvoiceID & "'"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("RTNDATA")
        Try
            da.Fill(ds, "RTNDATA")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetWholesaleReturnDetails = ds
    End Function
    Public Function GetWholesaleLpoDetails(ByVal InvoiceID As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If IsNumeric(Left(InvoiceID, 1)) Then
            strsql = "select * from LpoData " & _
                     "where LpoID='" & InvoiceID & "'"
        Else
            strsql = "select * from LpoData " & _
                     "where LpoNo='" & InvoiceID & "'"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVDATA")
        Try
            da.Fill(ds, "INVDATA")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetWholesaleLpoDetails = ds
    End Function
    Public Function GetPurchaseRtnDetails(ByVal ReturnID As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from PurchaseRtnData " & _
                 "where PrID='" & ReturnID & "'"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("INVDATA")
        Try
            da.Fill(ds, "INVDATA")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetPurchaseRtnDetails = ds
    End Function
    Public Function GetOrderDetails(ByVal OrderID As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from PurchaseOrderData " & _
                 "where PoID='" & OrderID & "'"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ORDRDATA")
        Try
            da.Fill(ds, "ORDRDATA")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetOrderDetails = ds
    End Function
    Public Function GetProductPendingOrders(ByVal CompanyID As Integer, ByVal ProductCode As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from PurchaseOrderData " & _
                 "where PoProductCode='" & ProductCode & "'"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ORDRDATA")
        Try
            da.Fill(ds, "ORDRDATA")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetProductPendingOrders = ds
    End Function
    Public Function GetShipmentDetails(ByVal OrderID As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from ShipmentData " & _
                 "where ShID='" & OrderID & "'"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SHIPDATA")
        Try
            da.Fill(ds, "SHIPDATA")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetShipmentDetails = ds
    End Function
    Public Function GetOrderHeaderData(ByVal OrderID As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from PurchaseOrderHead " & _
                 "where PoID='" & OrderID & "'"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("ORDRHEAD")
        Try
            da.Fill(ds, "ORDRHEAD")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetOrderHeaderData = ds
    End Function
    Public Function GetShipmentHeaderData(ByVal OrderID As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from ShipmentHead " & _
                 "where ShID='" & OrderID & "'"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SHIPHEAD")
        Try
            da.Fill(ds, "SHIPHEAD")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetShipmentHeaderData = ds
    End Function
    Public Function GetShipmentByCurrency(ByVal CompanyID As Integer, ByVal CurrencyCode As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select ShID,ShNumber from ShipmentHead " & _
                "where ShID in (select distinct(shID) ShID " & _
                "from ShipmentData b,PurchaseOrderHead a " & _
                "where b.ShPoID=a.PoID and a.PoCurrency='" & CurrencyCode & "' " & _
                "and a.PoCompanyID=" & CompanyID & ") and " & _
                "shCompanyID=" & CompanyID & " and shStatus='SAVE'"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("SHIPHEAD")
        Try
            da.Fill(ds, "SHIPHEAD")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetShipmentByCurrency = ds
    End Function
    Public Function GetPosCusData(ByVal ProfileID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from PosCustomer " & _
                 "where ProfileID='" & ProfileID & "'"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("PROFILE")
        Try
            da.Fill(ds, "PROFILE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetPosCusData = ds
    End Function
    Public Function GetPosCustomers(ByVal GroupID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        If GroupID = 0 Then
            strsql = "select * from PosCustomer "
        Else
            strsql = "select * from PosCustomer " & _
                     "where CusGroup='" & GroupID & "'"
        End If
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("PROFILE")
        Try
            da.Fill(ds, "PROFILE")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetPosCustomers = ds
    End Function
    Public Function GetPosCusRequest(ByVal RequestID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Select a.*,b.* from PosCusRequest a,PosCustomer b " & _
                    "where a.ReqProfileID = b.ProfileID and RequestID='" & RequestID & "'"
        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("REQUEST")
        Try
            da.Fill(ds, "REQUEST")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetPosCusRequest = ds
    End Function
    Public Sub InsertProductData(ByVal CompanyID As Integer, ByVal BrandID As Integer, ByVal ManufProdCode As String, _
                ByVal ProductCode As String, ByVal ShortE As String, ByVal ShortA As String, _
                ByVal LongE As String, ByVal LongA As String, ByVal Group2 As Integer, _
                ByVal Group3 As Integer, ByVal Group4 As Integer, ByVal Group5 As Integer, _
                ByVal Price1 As String, ByVal Price2 As String, ByVal Price3 As String, _
                ByVal Markup1 As String, ByVal Markup2 As String, ByVal Markup3 As String, _
                ByVal SaleUnit As String, ByVal RolQty As String, ByVal LeadTime As String, _
                ByVal LeadTimePeriod As String, ByVal ItemType As Integer, ByVal OuterPackSize As String, _
                ByVal PackVolumeType As String, ByVal ImagePath As String, ByVal ActiveFlag As Integer, _
                ByVal PromotionFlag As Integer, ByVal KitFlag As Integer, ByVal WhFlag As Integer, _
                ByVal ShowroomsRolXml As String, ByVal BarCodesXml As String, ByVal ProdSupXml As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)

        Dim strsql As String
        strsql = "Insert into Product(CompanyCode,ProductCode,ProductType,ManufProductCode," & _
                "ShortDescriptionE,ShortDescriptionA,LongDescriptionE,LongDescriptionA," & _
                "Group1Code,Group2Code,Group3Code,Group4Code,Group5Code,FirstPrice,SecondPrice,ThirdPrice," & _
                "SaleUnit,FirstMarkupPercent,SecondMarkupPercent,ThirdMarkupPercent," & _
                "OuterPackSize,PackVolumeType,LeadTime,LeadTimeType,ImageFileName," & _
                "RolQty,ActiveFlag,OnPromotionFlag,KitFlag,WhProductFlag) " & _
                "Values(" & CompanyID & ",'" & ProductCode + "'," & GetNumDefault(ItemType) & "," & _
                "'" & ManufProdCode & "','" & ShortE & "','" & ShortA & "','" & LongE & "'," & _
                "'" & LongA & "'," & BrandID & "," & Group2 & "," & Group3 & "," & Group4 & "," & Group5 & "," & _
                GetNumDefault(Price1) & "," & GetNumDefault(Price2) & "," & GetNumDefault(Price3) & ",'" & _
                SaleUnit & "'," & GetNumDefault(Markup1) & "," & GetNumDefault(Markup2) & "," & GetNumDefault(Markup3) & "," & _
                "'" & OuterPackSize & "','" & PackVolumeType & "'," & GetNumDefault(LeadTime) & ",'" & LeadTimePeriod & "','" & ImagePath & "'," & _
                GetNumDefault(RolQty) & "," & ActiveFlag & "," & PromotionFlag & "," & KitFlag & "," & WhFlag & ") " & _
                "Select @@Identity "
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim ProdID As Integer
        Try
            ProdID = sqlCmd.ExecuteScalar()
        Catch ex As SqlException
            InsertError(ex.Procedure, ex.LineNumber, ex.Number, ex.ToString())
            Exit Sub
        End Try
        Dim Xdoc As XmlDocument = New XmlDocument
        Xdoc.LoadXml(ShowroomsRolXml)
        Dim ShowID As Integer
        Dim ShowRolQty As Decimal
        Dim RootNode As XmlElement = Xdoc.DocumentElement
        Dim nodeList As XmlNodeList = RootNode.GetElementsByTagName("Showroom")
        Dim i As Integer
        For i = 0 To nodeList.Count - 1
            ShowID = Integer.Parse(nodeList.Item(i).ChildNodes(0).InnerXml())
            ShowRolQty = Integer.Parse(nodeList.Item(i).ChildNodes(1).InnerXml())
            strsql = "Insert into ShowroomRol (ProductID,ShowroomID,MinimumQty) " & _
                    "values(" & ProdID & "," & ShowID & "," & ShowRolQty & ")"
            sqlCmd.CommandText = strsql
            sqlCmd.ExecuteNonQuery()
        Next i
        '
        Xdoc.LoadXml(ProdSupXml)
        Dim SupID As Integer
        RootNode = Xdoc.DocumentElement
        nodeList = RootNode.GetElementsByTagName("Supplier")
        For i = 0 To nodeList.Count - 1
            SupID = Integer.Parse(nodeList.Item(i).ChildNodes(0).InnerXml())
            strsql = "Insert into ProductSupplier (SupID,ProdID,CompanyID) " & _
                    "values(" & SupID & "," & ProdID & "," & CompanyID & ")"
            sqlCmd.CommandText = strsql
            sqlCmd.ExecuteNonQuery()
        Next i
        '
        Xdoc.LoadXml(BarCodesXml)
        Dim BarCode As String
        RootNode = Xdoc.DocumentElement
        nodeList = RootNode.GetElementsByTagName("BarCode")
        For i = 0 To nodeList.Count - 1
            BarCode = nodeList.Item(i).InnerXml()
            strsql = "Insert into BarCode (ProductID,ProductBarCode) " & _
                    "values(" & ProdID & ",'" & BarCode & "')"
            sqlCmd.CommandText = strsql
            sqlCmd.ExecuteNonQuery()
        Next i
        sqlCon.Close()
    End Sub
    Public Function ProcessCusProdMatrix(ByVal InvXml As String) As String
        Dim Xdoc As XmlDocument = New XmlDocument
        Xdoc.LoadXml(InvXml)
        Dim RootNode As XmlElement = Xdoc.DocumentElement
        Dim CompanyID As Integer = RootNode.SelectSingleNode("InvoiceHead/CompanyID").InnerText
        Dim CustomerID As String = RootNode.SelectSingleNode("InvoiceHead/CustomerID").InnerText
        Dim nodeList As XmlNodeList = RootNode.GetElementsByTagName("InvLine")
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql)
        sqlCmd.Connection = sqlCon
        sqlCon.Open()

        Dim i As Integer
        For i = 0 To nodeList.Count - 1
            Dim ProdID As String = nodeList.Item(i).ChildNodes(0).InnerText
            Dim ProdCode As String = nodeList.Item(i).ChildNodes(1).InnerText
            strsql = "Insert into CustomerProduct (CompanyID,CustomerID,ProductID,CustomerProdCode) " & _
                    "Values (" & CompanyID & "," & CustomerID & "," & ProdID & "," & _
                    "'" & ProdCode & "')"
            sqlCmd.CommandText = strsql
            Try
                sqlCmd.ExecuteNonQuery()
            Catch ex As SqlException
                InsertError("ProcessCusProdMatrix", ex.LineNumber, ex.Number, ex.ToString())
                Return "ERROR"
            End Try
        Next i
        sqlCon.Close()
        Return "OK"
    End Function
    Public Sub UpdateProductData(ByVal ProdID As Integer, ByVal BrandID As Integer, ByVal ManufProdCode As String, _
                ByVal ProductCode As String, ByVal ShortE As String, ByVal ShortA As String, _
                ByVal LongE As String, ByVal LongA As String, ByVal Group2 As Integer, _
                ByVal Group3 As Integer, ByVal Group4 As Integer, ByVal Group5 As Integer, _
                ByVal Price1 As String, ByVal Price2 As String, ByVal Price3 As String, _
                ByVal Markup1 As String, ByVal Markup2 As String, ByVal Markup3 As String, _
                ByVal SaleUnit As String, ByVal RolQty As String, ByVal LeadTime As String, _
                ByVal LeadTimePeriod As String, ByVal ItemType As Integer, ByVal OuterPackSize As String, _
                ByVal PackVolumeType As String, ByVal ImagePath As String, ByVal ActiveFlag As Integer, _
                ByVal PromotionFlag As Integer, ByVal KitFlag As Integer, ByVal WhFlag As Integer, _
                ByVal ShowroomsRolXml As String, ByVal BarCodesXml As String, ByVal ProdSupXml As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)

        Dim strsql As String
        strsql = "Update Product set " & _
            "ProductCode='" & ProductCode & "',ProductType=" & GetNumDefault(ItemType) & "," & _
            "ManufProductCode='" & ManufProdCode & "',ShortDescriptionE='" & ShortE & "'," & _
            "ShortDescriptionA='" & ShortA & "',LongDescriptionE='" & LongE & "'," & _
            "LongDescriptionA='" & LongA & "',Group1Code=" & BrandID & ",Group2Code=" & Group2 & "," & _
            "Group3Code=" & Group3 & ",Group4Code=" & Group4 & ",Group5Code=" & Group5 & "," & _
            "FirstPrice=" & GetNumDefault(Price1) & "," & "SecondPrice=" & GetNumDefault(Price2) & "," & _
            "ThirdPrice=" & GetNumDefault(Price3) & ",FirstMarkupPercent=" & GetNumDefault(Markup1) & "," & _
            "SecondMarkupPercent=" & GetNumDefault(Markup2) & ",ThirdMarkupPercent=" & GetNumDefault(Markup3) & "," & _
            "SaleUnit='" & SaleUnit & "',OuterPackSize='" & OuterPackSize & "',PackVolumeType='" & PackVolumeType & "'," & _
            "LeadTime=" & GetNumDefault(LeadTime) & ",LeadtimeType='" & LeadTimePeriod & "',ImageFileName='" & ImagePath & "'," & _
            "RolQty=" & GetNumDefault(RolQty) & ",ActiveFlag=" & ActiveFlag & ",OnPromotionFlag=" & PromotionFlag & "," & _
            "KitFlag=" & KitFlag & ",WhProductFlag=" & WhFlag & _
            " where ProductID=" & ProdID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Try
            sqlCmd.ExecuteNonQuery()
        Catch ex As SqlException
            InsertError(ex.Source, ex.LineNumber, ex.Number, ex.ToString())
            Exit Sub
        End Try
        Dim Xdoc As XmlDocument = New XmlDocument
        Xdoc.LoadXml(ShowroomsRolXml)
        Dim ShowID As Integer
        Dim ShowRolQty As Decimal
        Dim RootNode As XmlElement = Xdoc.DocumentElement
        Dim nodeList As XmlNodeList = RootNode.GetElementsByTagName("Showroom")
        Dim i As Integer
        For i = 0 To nodeList.Count - 1
            ShowID = Integer.Parse(nodeList.Item(i).ChildNodes(0).InnerXml())
            ShowRolQty = Integer.Parse(nodeList.Item(i).ChildNodes(1).InnerXml())
            strsql = "Update ShowroomRol set MinimumQty=" & ShowRolQty & _
                    " where ProductID=" & ProdID & " and ShowroomID=" & ShowID
            sqlCmd.CommandText = strsql
            sqlCmd.ExecuteNonQuery()
        Next i

        strsql = "Delete from BarCode where ProductID=" & ProdID
        sqlCmd.CommandText = strsql
        sqlCmd.ExecuteNonQuery()

        Xdoc.LoadXml(BarCodesXml)
        Dim BarCode As String
        RootNode = Xdoc.DocumentElement
        nodeList = RootNode.GetElementsByTagName("BarCode")
        For i = 0 To nodeList.Count - 1
            BarCode = nodeList.Item(i).InnerXml()
            strsql = "Insert into BarCode (ProductID,ProductBarCode) " & _
                    "values(" & ProdID & ",'" & BarCode & "')"
            sqlCmd.CommandText = strsql
            sqlCmd.ExecuteNonQuery()
        Next i
        '
        strsql = "Delete from ProductSupplier where ProductID=" & ProdID
        sqlCmd.CommandText = strsql
        sqlCmd.ExecuteNonQuery()
        '
        Xdoc.LoadXml(ProdSupXml)
        Dim SupID As Integer
        RootNode = Xdoc.DocumentElement
        nodeList = RootNode.GetElementsByTagName("Supplier")
        For i = 0 To nodeList.Count - 1
            SupID = Integer.Parse(nodeList.Item(i).ChildNodes(0).InnerXml())
            strsql = "Insert into ProductSupplier (SupID,ProdID,CompanyID) " & _
                    "values(" & SupID & "," & ProdID & "," & CompanyID & ")"
            sqlCmd.CommandText = strsql
            sqlCmd.ExecuteNonQuery()
        Next i
        '
        sqlCon.Close()
    End Sub
    Public Sub UpdateGroupData(ByVal GroupID As Integer, _
                ByVal GroupName As String, ByVal AccountReport As String, ByVal AccountType As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)

        Dim strsql As String
        strsql = "Update AccountGroup set " & _
            "GroupName='" & GroupName & "',ReportType='" & AccountReport & "',AccountType='" & AccountType & "' " & _
            " where GroupID=" & GroupID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Try
            sqlCmd.ExecuteNonQuery()
        Catch ex As SqlException
            InsertError(ex.Source, ex.LineNumber, ex.Number, ex.ToString())
            Exit Sub
        End Try
    End Sub
    Public Function GetNumDefault(ByVal NumericField As String)
        If NumericField.ToString.Trim = "" Then
            Return "0"
        Else
            Return NumericField.Replace(",", "")
        End If
    End Function
    Public Sub DeleteProduct(ByVal ProdID As Integer)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Delete from Product where ProductID=" & ProdID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Sub DeleteAccount(ByVal AccountID As Integer)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Delete from Account where AccountID=" & AccountID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Sub DeleteBudget(ByVal BudgetID As Integer)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Delete from Budget where BudID=" & BudgetID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Sub DeleteAccountGroup(ByVal GroupID As Integer)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Delete from AccountGroup where GroupID=" & GroupID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Sub InsertAccountGroup(ByVal GroupName As String, ByVal GroupReport As String, ByVal GroupType As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Insert into AccountGroup (GroupName,ReportType,AccountType) " & _
                "values('" & GroupName & "','" & GroupReport & "','" & GroupType & "')"
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Public Sub InsertCustomerDetails(ByVal CompanyID As Integer, ByVal GroupID As Integer, ByVal CusName As String, _
                ByVal Adr1 As String, ByVal Adr2 As String, ByVal Dadr1 As String, ByVal Dadr2 As String, _
                ByVal Email As String, ByVal Website As String, ByVal Phone As String, _
                ByVal Fax As String, ByVal Contact As String, ByVal CusXml As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Insert into Customer(CompanyID,CusGroupID,CusName," & _
                "CusAdr1,CusAdr2,CusDadr1,CusDadr2,CusEmail,CusWebUrl,CusTelephone,CusFax,CusContact) " & _
                "values(" & CompanyID & "," & GroupID & "," & _
                "'" & CusName & "','" & Adr1 & "','" & Adr2 & "','" & Dadr1 & "','" & Dadr2 & "'," & _
                "'" & Email & "','" & Website & "','" & Phone & "'," & _
                "'" & Fax & "','" & Contact & "') " & _
                "select @@identity"
        sqlCon.Open()
        Dim CusID As Integer
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Try
            CusID = sqlCmd.ExecuteScalar
        Catch ex As Exception
            MsgBox(ex.ToString())
        End Try
        Dim Xdoc As XmlDocument = New XmlDocument
        Xdoc.LoadXml(CusXml)
        Dim IncludeCus As Boolean
        Dim CrLimit As String
        Dim ThisCompany As Integer
        Dim RootNode As XmlElement = Xdoc.DocumentElement
        Dim nodeList As XmlNodeList = RootNode.GetElementsByTagName("InvLine")
        Dim i As Integer
        For i = 0 To nodeList.Count - 1
            IncludeCus = nodeList.Item(i).ChildNodes(1).InnerXml()
            ThisCompany = Integer.Parse(nodeList.Item(i).ChildNodes(0).InnerXml())
            CrLimit = nodeList.Item(i).ChildNodes(2).InnerXml()
            If CrLimit = "" Then
                CrLimit = "0"
            End If
            If IncludeCus = True Then
                strsql = "Insert into CompanyCustomer (CusID,CompanyID,CreditLimit) " & _
                        "values(" & CusID & "," & ThisCompany & ",'" & CrLimit & "')"
                sqlCmd.CommandText = strsql
                sqlCmd.ExecuteNonQuery()
            End If
        Next i
        sqlCon.Close()
    End Sub
    Public Sub UpdateCustomerDetails(ByVal CusID As Integer, ByVal GroupID As Integer, ByVal CusName As String, _
                ByVal Adr1 As String, ByVal Adr2 As String, ByVal Dadr1 As String, ByVal Dadr2 As String, _
                ByVal Email As String, ByVal Website As String, ByVal Phone As String, _
                ByVal Fax As String, ByVal Contact As String, ByVal CusXml As String)
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim sqlCon2 As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Update Customer set CusGroupID=" & GroupID & "," & _
                "CusName='" & CusName & "',CusAdr1='" & Adr1 & "',CusAdr2='" & Adr2 & "',CusDadr1='" & Dadr1 & "',CusDadr2='" & Dadr2 & "'," & _
                "CusEmail='" & Email & "',CusWebUrl='" & Website & "',CusTelephone='" & Phone & "'," & _
                "CusFax='" & Fax & "',CusContact='" & Contact & "' " & _
                "Where CusID=" & CusID
        sqlCon.Open()
        sqlCon2.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Try
            sqlCmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.ToString())
        End Try
        Dim Xdoc As XmlDocument = New XmlDocument
        Xdoc.LoadXml(CusXml)
        Dim IncludeCus As Boolean
        Dim CrLimit As String
        Dim ThisCompany As Integer
        Dim RootNode As XmlElement = Xdoc.DocumentElement
        Dim nodeList As XmlNodeList = RootNode.GetElementsByTagName("InvLine")
        Dim i As Integer
        For i = 0 To nodeList.Count - 1
            IncludeCus = nodeList.Item(i).ChildNodes(1).InnerXml()
            ThisCompany = Integer.Parse(nodeList.Item(i).ChildNodes(0).InnerXml())
            CrLimit = nodeList.Item(i).ChildNodes(2).InnerXml()
            If CrLimit = "" Then
                CrLimit = "0"
            End If
            If IncludeCus = True Then
                strsql = "Select count(*) from CompanyCustomer where CompanyID=" & ThisCompany & " and CusID=" & CusID
                Dim sqlCmd2 As SqlCommand = New SqlCommand(strsql, sqlCon2)
                If sqlCmd2.ExecuteScalar = 0 Then
                    strsql = "Insert into CompanyCustomer (CusID,CompanyID,CreditLimit) " & _
                            "values(" & CusID & "," & ThisCompany & ",'" & CrLimit & "')"
                Else
                    strsql = "Update CompanyCustomer set CreditLimit='" & CrLimit & "' " & _
                            "where CusID=" & CusID & " and CompanyID=" & ThisCompany
                End If
                sqlCmd.CommandText = strsql
                sqlCmd.ExecuteNonQuery()
            Else
                strsql = "Select count(*) from CompanyCustomer where CompanyID=" & ThisCompany & " and CusID=" & CusID
                Dim sqlCmd2 As SqlCommand = New SqlCommand(strsql, sqlCon2)
                If sqlCmd2.ExecuteScalar > 0 Then
                    strsql = "Delete from CompanyCustomer " & _
                            "where CusID=" & CusID & " and CompanyID=" & ThisCompany
                    sqlCmd.CommandText = strsql
                    sqlCmd.ExecuteNonQuery()
                End If
            End If
        Next i
        sqlCon.Close()
    End Sub
    Public Sub InsertError(ByVal ErrModule As String, ByVal ErrLine As String, ByVal ErrNumber As String, ByVal ErrDescription As String)
        'On Error Resume Next
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Insert into ErrorLog (ErrModule,ErrLine,ErrNumber,ErrDescription) " & _
                "values('" & ErrModule & "','" & ErrLine & "','" & ErrNumber & "','" & ErrDescription.Replace("'", "") & "')"
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        sqlCmd.ExecuteNonQuery()
        sqlCon.Close()
    End Sub
    Private Function PrefixInteger(ByVal strVal As String) As String
        If strVal.Trim = "" Then
            Return "0"
        Else
            Return strVal.Replace(",", "")
        End If
    End Function
    Private Function ValidDate(ByVal strVal As String) As String
        If strVal.Trim = "" Then
            Return "0"
        Else
            Dim dd As String
            Dim mm As String
            Dim yyyy As String
            dd = strVal.Substring(0, 2)
            mm = strVal.Substring(3, 2)
            yyyy = strVal.Substring(6, 4)
            strVal = mm & "/" & dd & "/" & yyyy
            Return strVal
        End If
    End Function
    Public Function GetSupplierEmail(ByVal SupplierID As Integer) As String
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Select SupEmail from Supplier where SupID=" & SupplierID
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim NextInv As String
        Dim rdr As SqlDataReader
        rdr = sqlCmd.ExecuteReader
        While rdr.Read
            NextInv = CStr(rdr("SupEmail"))
        End While
        Dim nlen As Integer
        sqlCon.Close()
        Return NextInv
    End Function
    Public Function ProcessGLtrxData(ByVal CompanyID As Integer, ByVal TrxStatus As String, ByVal TrxXml As String) As String
        Dim Xdoc As XmlDocument = New XmlDocument
        Xdoc.LoadXml(TrxXml)
        Dim RootNode As XmlElement = Xdoc.DocumentElement
        Dim InvNum As String = RootNode.SelectSingleNode("InvoiceHead/TrxNum").InnerText

        Dim TrxDate As String = ValidDate(RootNode.SelectSingleNode("InvoiceHead/TrxDate").InnerText)
        Dim TrxSource As String = RootNode.SelectSingleNode("InvoiceHead/TrxSource").InnerText
        Dim TrxType As String = RootNode.SelectSingleNode("InvoiceHead/TrxType").InnerText

        Dim nodeList As XmlNodeList = RootNode.GetElementsByTagName("InvLine")
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql)
        sqlCmd.Connection = sqlCon
        sqlCon.Open()
        If TrxStatus = "SAVE" Then
            If InvNum.Trim <> "" Then
                strsql = "Delete from GjTrx where TrxNumber='" & InvNum & "'"
                sqlCmd.CommandText = strsql
                sqlCmd.ExecuteNonQuery()
            Else
                InvNum = GetNextTrxNumber(CompanyID, TrxType)
            End If
            Dim i As Integer
            For i = 0 To nodeList.Count - 1
                Dim MainAccount As String = nodeList.Item(i).ChildNodes(0).InnerText
                Dim SubDpt As String = nodeList.Item(i).ChildNodes(1).InnerText
                Dim AcDesc As String = nodeList.Item(i).ChildNodes(2).InnerText
                Dim Voucher As String = nodeList.Item(i).ChildNodes(3).InnerText
                Dim TrxRefer As String = nodeList.Item(i).ChildNodes(4).InnerText
                Dim TrxDebit As String = PrefixInteger(nodeList.Item(i).ChildNodes(5).InnerText)
                Dim TrxCredit As String = PrefixInteger(nodeList.Item(i).ChildNodes(6).InnerText)
                Dim SubType As String = nodeList.Item(i).ChildNodes(7).InnerText
                Dim SubAcID As String = nodeList.Item(i).ChildNodes(8).InnerText
                Dim SubDoc As String = nodeList.Item(i).ChildNodes(9).InnerText
                Dim SubTrxType As String = nodeList.Item(i).ChildNodes(10).InnerText
                Dim SubRefer As String = nodeList.Item(i).ChildNodes(11).InnerText
                Dim SubFcCode As String = nodeList.Item(i).ChildNodes(12).InnerText
                Dim SubFcAmt As String = PrefixInteger(nodeList.Item(i).ChildNodes(13).InnerText)
                Dim Salesman As String = PrefixInteger(nodeList.Item(i).ChildNodes(14).InnerText)
                strsql = "Insert into GjTrx (CompanyID,AcNum,AcCent,AcDesc,TrxDate,TrxAmtDr,TrxAmtCr,Trxsource," & _
                        "TrxRefer,TrxNumber,SubArAp,SubDoc,SubType,SubNum,SubRefer,SubFc,SubFCode,UserID,Voucher,Salesman) " & _
                        "Values ('" & CompanyID & "','" & MainAccount & "','" & SubDpt & "'," & _
                        "'" & AcDesc & "','" & TrxDate & "'," & _
                        "" & TrxDebit & "," & TrxCredit & ",'" & TrxSource & "','" & TrxRefer & "'," & _
                        "'" & InvNum & "','" & SubType & "'," & _
                        "'" & SubDoc & "','" & SubTrxType & "','" & SubAcID & "'," & _
                        "'" & SubRefer & "','" & SubFcAmt & "','" & SubFcCode & "'," & UserID & ",'" & _
                        "" & Voucher & "','" & Salesman & "')"
                sqlCmd.CommandText = strsql
                Try
                    sqlCmd.ExecuteNonQuery()
                Catch ex As SqlException
                    InsertError("ProcessGLtrxData", ex.LineNumber, ex.Number, ex.ToString())
                    MessageBox.Show("e1" & ex.ToString())
                    Return "ERROR"
                End Try
            Next i
            sqlCon.Close()
        Else
            If InvNum.Trim <> "" Then
                strsql = "Delete from GjTrx where TrxNumber='" & InvNum & "'"
                sqlCmd.CommandText = strsql
                Try
                    sqlCmd.ExecuteNonQuery()
                Catch ex As SqlException
                    InsertError("ProcessGLtrxData", ex.LineNumber, ex.Number, ex.ToString())
                    MessageBox.Show("e2" & ex.ToString())
                    Return "ERROR"
                End Try
            End If
        End If
        Return InvNum
    End Function
    Public Function PostGLtrxData(ByVal CompanyID As Integer, ByVal TrxStatus As String, ByVal TrxXml As String) As String
        Dim Xdoc As XmlDocument = New XmlDocument
        Xdoc.LoadXml(TrxXml)
        Dim RootNode As XmlElement = Xdoc.DocumentElement
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql)
        sqlCmd.Connection = sqlCon
        sqlCon.Open()
        Dim sqlTrans As SqlTransaction
        sqlTrans = sqlCon.BeginTransaction
        sqlCmd.Transaction = sqlTrans

        Dim sqlCon2 As SqlConnection = New SqlConnection(strCon)
        Dim sqlCmd2 As SqlCommand = New SqlCommand(strsql)

        sqlCmd2.Connection = sqlCon2
        sqlCon2.Open()
        Dim nodeList As XmlNodeList = RootNode.GetElementsByTagName("TrxNum")
        Dim i As Integer
        Dim TrxNum As String

        Dim sqlCon5 As SqlConnection = New SqlConnection(strCon)
        Dim sqlCmd5 As SqlCommand = New SqlCommand(strsql)
        sqlCmd5.Connection = sqlCon5
        sqlCon5.Open()
        Dim rdr As SqlDataReader
        For i = 0 To nodeList.Count - 1
            TrxNum = nodeList.Item(i).InnerText
            strsql = "select * from GjTrx nolock where TrxNumber='" & TrxNum & "' and CompanyID='" & CompanyID & "' and TrxStatus='SAVE'"
            sqlCmd5.CommandText = strsql
            rdr = sqlCmd5.ExecuteReader()
            While rdr.Read
                Dim MainAccount As String = rdr("AcNum")
                Dim SubDpt As String = rdr("AcCent")
                Dim TrxSource As String = rdr("TrxSource")
                Dim TrxDate As String = rdr("TrxDate")
                TrxDate = TrxDate.Substring(6, 4) & "-" & TrxDate.Substring(3, 2) & "-" & TrxDate.Substring(0, 2)
                Dim TrxRefer As String = rdr("TrxRefer")
                Dim Voucher As String = rdr("VOUCHER")
                Dim Salesman As String = rdr("Salesman")
                Dim TrxDebit As String = PrefixInteger(rdr("TrxAmtDr"))
                Dim TrxCredit As String = PrefixInteger(rdr("TrxAmtCr"))
                Dim SubType As String = rdr("SubArAp")
                Dim SubAcID As String = PrefixInteger(rdr("SubNum"))
                Dim SubDoc As String = rdr("SubDoc")
                Dim SubTrxType As String = rdr("SubType")
                Dim SubRefer As String = rdr("SubRefer")
                Dim SubFcCode As String = rdr("SubFcode")
                Dim SubFcAmt As String = PrefixInteger(rdr("SubFc"))
                Dim TrxAmt, TrxFcAmt As Double
                If CDbl(TrxDebit) > 0 Then
                    TrxAmt = CDbl(TrxDebit)
                    TrxFcAmt = CDbl(SubFcAmt)
                Else
                    TrxAmt = 0 - CDbl(TrxCredit)
                    TrxFcAmt = 0 - CDbl(SubFcAmt)
                End If
                strsql = "Insert into GLTrx (CompanyID,AcNum,AcCent,TrxDate,TrxAmt,Trxsource," & _
                        "TrxRefer,TrxNumber,VOUCHER) " & _
                        "Values ('" & CompanyID & "','" & MainAccount & "','" & SubDpt & "'," & _
                        "'" & TrxDate & "','" & TrxAmt & "','" & TrxSource & "','" & TrxRefer & "'," & _
                        "'" & TrxNum & "','" & Voucher & "')"
                sqlCmd.CommandText = strsql
                Try
                    sqlCmd.ExecuteNonQuery()
                Catch ex As SqlException
                    sqlTrans.Rollback()
                    InsertError("PostGLtrxData", ex.LineNumber, ex.Number, ex.ToString())
                    Return "ERROR"
                End Try
                If SubType = "AR" Then
                    strsql = "Insert into ArTrx (CusID,CompanyID,DocNum,DocDate,DocType,DocAmt,DocRefer,TrxNumber,Docsman) " & _
                            "values('" & SubAcID & "','" & CompanyID & "','" & SubDoc & "','" & TrxDate & "','" & SubTrxType & "','" & TrxAmt & "','" & SubRefer & "','" & TrxNum & "','" & Salesman & "')"
                    sqlCmd.CommandText = strsql
                    Try
                        sqlCmd.ExecuteNonQuery()
                    Catch ex As SqlException
                        sqlTrans.Rollback()
                        InsertError("PostGLtrxData", ex.LineNumber, ex.Number, ex.ToString())
                        Return "ERROR"
                    End Try
                End If
                If SubType = "AP" Then
                    strsql = "Insert into ApTrx (SupID,CompanyID,DocNum,DocDate,DocType,DocAmt,DocRefer,TrxNumber,DocFcCode,DocFcAmt) " & _
                            "values('" & SubAcID & "','" & CompanyID & "','" & SubDoc & "','" & TrxDate & "','" & SubTrxType & "','" & TrxAmt & "','" & SubRefer & "','" & TrxNum & "','" & SubFcCode & "','" & TrxFcAmt & "')"
                    sqlCmd.CommandText = strsql
                    Try
                        sqlCmd.ExecuteNonQuery()
                    Catch ex As SqlException
                        sqlTrans.Rollback()
                        InsertError("PostGLtrxData", ex.LineNumber, ex.Number, ex.ToString())
                        Return "ERROR"
                    End Try
                End If
            End While
            rdr.Close()
            'strsql = "Update GjTrx  set TrxStatus='POSTED' where TrxNumber='" & TrxNum & "' and CompanyId='" & CompanyID & "'"
            'sqlCmd.CommandText = strsql
            'Try
            '    sqlCmd.ExecuteNonQuery()
            'Catch ex As SqlException
            '    sqlTrans.Rollback()
            '    InsertError("PostGLtrxData", ex.LineNumber, ex.Number, ex.ToString())
            '    Return "ERROR"
            'End Try
        Next
        i = 0
        For i = 0 To nodeList.Count - 1
            TrxNum = nodeList.Item(i).InnerText
            strsql = "Update GjTrx  set TrxStatus='POSTED' where TrxNumber='" & TrxNum & "' and CompanyId='" & CompanyID & "'"
            sqlCmd.CommandText = strsql
            Try
                sqlCmd.ExecuteNonQuery()
            Catch ex As SqlException
                sqlTrans.Rollback()
                InsertError("PostGLtrxData", ex.LineNumber, ex.Number, ex.ToString())
                Return "ERROR"
            End Try
        Next
        sqlTrans.Commit()
        sqlCon.Close()
        sqlCon2.Close()
        sqlCon5.Close()

        Return "OK"
    End Function
    Public Function PostRetailGLtrxData(ByVal CompanyID As Integer, ByVal TrxXml As String, ByVal UserID As Integer) As String
        Dim Xdoc As XmlDocument = New XmlDocument
        Xdoc.LoadXml(TrxXml)
        Dim RootNode As XmlElement = Xdoc.DocumentElement
        Dim TrxNumber As String = ""
        TrxNumber = GetNextTrxNumber(CompanyID, "JV")

        Dim InvNum As String = TrxNumber

        Dim TrxDate As String = ValidDate(RootNode.SelectSingleNode("InvoiceHead/TrxDate").InnerText)
        Dim TrxSource As String = RootNode.SelectSingleNode("InvoiceHead/TrxSource").InnerText
        Dim TrxType As String = RootNode.SelectSingleNode("InvoiceHead/TrxType").InnerText

        Dim nodeList As XmlNodeList = RootNode.GetElementsByTagName("InvLine")
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql)
        sqlCmd.Connection = sqlCon
        sqlCon.Open()
        Dim sqlTrans As SqlTransaction
        sqlTrans = sqlCon.BeginTransaction
        sqlCmd.Transaction = sqlTrans

        Dim i As Integer
        For i = 0 To nodeList.Count - 1
            Dim MainAccount As String = nodeList.Item(i).ChildNodes(0).InnerText
            Dim SubDpt As String = nodeList.Item(i).ChildNodes(1).InnerText
            Dim AcDesc As String = nodeList.Item(i).ChildNodes(2).InnerText
            Dim TrxRefer As String = nodeList.Item(i).ChildNodes(3).InnerText
            Dim TrxDebit As String = PrefixInteger(nodeList.Item(i).ChildNodes(4).InnerText)
            Dim TrxCredit As String = PrefixInteger(nodeList.Item(i).ChildNodes(5).InnerText)
            Dim TrxAmt As Decimal = 0
            If Decimal.Parse(TrxDebit) > 0 Then
                TrxAmt = Decimal.Parse(TrxDebit)
            Else
                TrxAmt = Decimal.Parse(0 - TrxCredit)
            End If
            strsql = "Insert into GLTrx (CompanyID,AcNum,AcCent,TrxDate,TrxAmt,Trxsource," & _
                    "TrxRefer,TrxNumber) " & _
                    "Values ('" & CompanyID & "','" & MainAccount & "','" & SubDpt & "'," & _
                    "'" & TrxDate & "'," & _
                    "" & TrxAmt & ",'" & TrxSource & "','" & TrxRefer & "'," & _
                    "'" & InvNum & "')"
            sqlCmd.CommandText = strsql
            Try
                sqlCmd.ExecuteNonQuery()
            Catch ex As SqlException
                sqlTrans.Rollback()
                InsertError("PostRetailGLtrxData", ex.LineNumber, ex.Number, ex.ToString())
                Return "ERROR"
            End Try
        Next i
        sqlTrans.Commit()
        sqlCon.Close()
        Return TrxNumber
    End Function
    Public Function DeleteGJtrxData(ByVal CompanyID As Integer, ByVal TrxNum As String) As String
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql)
        sqlCmd.Connection = sqlCon
        sqlCon.Open()
        strsql = "Update GjTrx  set TrxStatus='DELETED' where TrxNumber='" & TrxNum & "' and CompanyId='" & CompanyID & "'"
        sqlCmd.CommandText = strsql
        Try
            sqlCmd.ExecuteNonQuery()
        Catch ex As SqlException
            InsertError("DeleteGJtrxData", ex.LineNumber, ex.Number, ex.ToString())
            Return "ERROR"
        End Try
        sqlCon.Close()
        Return "OK"
    End Function
    Public Function ProcessRecurData(ByVal CompanyID As Integer, ByVal TrxStatus As String, ByVal TrxXml As String) As String
        Dim Xdoc As XmlDocument = New XmlDocument
        Xdoc.LoadXml(TrxXml)
        Dim RootNode As XmlElement = Xdoc.DocumentElement
        Dim TrxName As String = RootNode.SelectSingleNode("InvoiceHead/TrxName").InnerText
        Dim TrxType As String = RootNode.SelectSingleNode("InvoiceHead/TrxType").InnerText

        Dim nodeList As XmlNodeList = RootNode.GetElementsByTagName("InvLine")
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql)
        sqlCmd.Connection = sqlCon
        sqlCon.Open()
        If TrxStatus = "SAVE" Then
            If TrxName.Trim <> "" Then
                strsql = "Delete from GjRecurTrx where TrxName='" & TrxName & "' and CompanyID=" & CompanyID
                sqlCmd.CommandText = strsql
                sqlCmd.ExecuteNonQuery()
            End If
            Dim i As Integer
            For i = 0 To nodeList.Count - 1
                Dim MainAccount As String = nodeList.Item(i).ChildNodes(0).InnerText
                Dim SubDpt As String = nodeList.Item(i).ChildNodes(1).InnerText
                Dim AcDesc As String = nodeList.Item(i).ChildNodes(2).InnerText
                Dim TrxRefer As String = nodeList.Item(i).ChildNodes(3).InnerText
                Dim TrxDebit As String = PrefixInteger(nodeList.Item(i).ChildNodes(4).InnerText)
                Dim TrxCredit As String = PrefixInteger(nodeList.Item(i).ChildNodes(5).InnerText)
                Dim SubType As String = nodeList.Item(i).ChildNodes(6).InnerText
                Dim SubAcID As String = nodeList.Item(i).ChildNodes(7).InnerText
                Dim SubDoc As String = nodeList.Item(i).ChildNodes(8).InnerText
                Dim SubTrxType As String = nodeList.Item(i).ChildNodes(9).InnerText
                Dim SubRefer As String = nodeList.Item(i).ChildNodes(10).InnerText
                Dim SubFcCode As String = nodeList.Item(i).ChildNodes(11).InnerText
                Dim SubFcAmt As String = PrefixInteger(nodeList.Item(i).ChildNodes(12).InnerText)
                strsql = "Insert into GjRecurTrx (CompanyID,AcNum,AcCent,AcDesc,TrxAmtDr,TrxAmtCr," & _
                        "TrxRefer,TrxName,SubArAp,SubDoc,SubType,SubNum,SubRefer,SubFc,SubFCode,UserID) " & _
                        "Values ('" & CompanyID & "','" & MainAccount & "','" & SubDpt & "'," & _
                        "'" & AcDesc & "'," & TrxDebit & "," & TrxCredit & ",'" & TrxRefer & "'," & _
                        "'" & TrxName & "','" & SubType & "'," & _
                        "'" & SubDoc & "','" & SubTrxType & "','" & SubAcID & "'," & _
                        "'" & SubRefer & "','" & SubFcAmt & "','" & SubFcCode & "'," & _
                        "'00')"
                sqlCmd.CommandText = strsql
                Try
                    sqlCmd.ExecuteNonQuery()
                Catch ex As SqlException
                    InsertError("ProcessRecurData", ex.LineNumber, ex.Number, ex.ToString())
                    Return "ERROR"
                End Try
            Next i
            sqlCon.Close()
        Else
            If TrxName.Trim <> "" Then
                strsql = "Delete from GjRecurTrx where TrxName='" & TrxName & "' and CompanyID=" & CompanyID
                sqlCmd.CommandText = strsql
                Try
                    sqlCmd.ExecuteNonQuery()
                Catch ex As SqlException
                    InsertError("ProcessRecurData", ex.LineNumber, ex.Number, ex.ToString())
                    Return "ERROR"
                End Try
            End If
        End If
        Return "OK"
    End Function
    Public Function PostRecurData(ByVal CompanyID As Integer, ByVal TrxStatus As String, ByVal TrxXml As String) As String
        Dim Xdoc As XmlDocument = New XmlDocument
        Xdoc.LoadXml(TrxXml)
        Dim RootNode As XmlElement = Xdoc.DocumentElement
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql)
        sqlCmd.Connection = sqlCon
        sqlCon.Open()
        Dim sqlTrans As SqlTransaction
        sqlTrans = sqlCon.BeginTransaction
        sqlCmd.Transaction = sqlTrans

        Dim sqlCon2 As SqlConnection = New SqlConnection(strCon)
        Dim sqlCmd2 As SqlCommand = New SqlCommand(strsql)
        sqlCmd2.Connection = sqlCon2
        sqlCon2.Open()
        Dim rdr As SqlDataReader
        Dim nodeList As XmlNodeList = RootNode.GetElementsByTagName("InvoiceHead")
        Dim i As Integer
        For i = 0 To nodeList.Count - 1
            Dim TrxName, TrxNum, TrxDate, TrxSource As String
            TrxName = nodeList.Item(i).ChildNodes(0).InnerText
            TrxDate = nodeList.Item(i).ChildNodes(1).InnerText
            TrxDate = TrxDate.Substring(6, 4) & "-" & TrxDate.Substring(3, 2) & "-" & TrxDate.Substring(0, 2)
            TrxSource = nodeList.Item(i).ChildNodes(2).InnerText
            strsql = "select * from GjRecurTrx where TrxName='" & TrxName & "' and CompanyID='" & CompanyID & "'"
            sqlCmd2.CommandText = strsql
            rdr = sqlCmd2.ExecuteReader
            Dim GotTrxNum As Boolean
            GotTrxNum = False
            While rdr.Read
                If GotTrxNum = False Then
                    TrxNum = GetNextTrxNumber(CompanyID, rdr("TrxDocType"))
                    GotTrxNum = False
                End If
                Dim MainAccount As String = rdr("AcNum")
                Dim SubDpt As String = rdr("AcCent")
                Dim TrxRefer As String = rdr("TrxRefer")
                Dim TrxDebit As String = PrefixInteger(rdr("TrxAmtDr"))
                Dim TrxCredit As String = PrefixInteger(rdr("TrxAmtCr"))
                Dim SubType As String = rdr("SubArAp")
                Dim SubAcID As String = PrefixInteger(rdr("SubNum"))
                Dim SubDoc As String = rdr("SubDoc")
                Dim SubTrxType As String = rdr("SubType")
                Dim SubRefer As String = rdr("SubRefer")
                Dim SubFcCode As String = rdr("SubFcode")
                Dim SubFcAmt As String = PrefixInteger(rdr("SubFc"))
                Dim TrxAmt, TrxFcAmt As Double
                If CDbl(TrxDebit) > 0 Then
                    TrxAmt = CDbl(TrxDebit)
                    TrxFcAmt = CDbl(SubFcAmt)
                Else
                    TrxAmt = 0 - CDbl(TrxCredit)
                    TrxFcAmt = 0 - CDbl(SubFcAmt)
                End If
                strsql = "Insert into GLTrx (CompanyID,AcNum,AcCent,TrxDate,TrxAmt,Trxsource," & _
                        "TrxRefer,TrxNumber) " & _
                        "Values ('" & CompanyID & "','" & MainAccount & "','" & SubDpt & "'," & _
                        "'" & TrxDate & "','" & TrxAmt & "','" & TrxSource & "','" & TrxRefer & "'," & _
                        "'" & TrxNum & "')"
                sqlCmd.CommandText = strsql
                Try
                    sqlCmd.ExecuteNonQuery()
                Catch ex As SqlException
                    sqlTrans.Rollback()
                    InsertError("PostRecurData", ex.LineNumber, ex.Number, ex.ToString())
                    Return "ERROR"
                End Try
                If SubType = "AR" Then
                    strsql = "Insert into ArTrx (CusID,CompanyID,DocNum,DocDate,DocType,DocAmt,DocRefer,TrxNumber) " & _
                            "values('" & SubAcID & "','" & CompanyID & "','" & SubDoc & "','" & TrxDate & "','" & SubTrxType & "','" & TrxAmt & "','" & SubRefer & "','" & TrxNum & "')"
                    sqlCmd.CommandText = strsql
                    Try
                        sqlCmd.ExecuteNonQuery()
                    Catch ex As SqlException
                        sqlTrans.Rollback()
                        InsertError("PostRecurData", ex.LineNumber, ex.Number, ex.ToString())
                        Return "ERROR"
                    End Try
                End If
                If SubType = "AP" Then
                    strsql = "Insert into ApTrx (SupID,CompanyID,DocNum,DocDate,DocType,DocAmt,DocRefer,TrxNumber,DocFcAmt,DocFcCode) " & _
                            "values('" & SubAcID & "','" & CompanyID & "','" & SubDoc & "','" & TrxDate & "','" & SubTrxType & "','" & TrxAmt & "','" & SubRefer & "','" & TrxNum & "','" & SubFcCode & "','" & TrxFcAmt & "')"
                    sqlCmd.CommandText = strsql
                    Try
                        sqlCmd.ExecuteNonQuery()
                    Catch ex As SqlException
                        sqlTrans.Rollback()
                        InsertError("PostRecurData", ex.LineNumber, ex.Number, ex.ToString())
                        Return "ERROR"
                    End Try
                End If
            End While
        Next
        sqlTrans.Commit()
        sqlCon.Close()
        sqlCon2.Close()
        Return "OK"
    End Function
    Public Function GetJournalTrx(ByVal CompanyID As Integer, ByVal TrxNumber As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from GjTrx " & _
                 "where TrxNumber='" & TrxNumber & "' and CompanyID=" & CompanyID & _
                 " order by TrxID "

        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("GJDATA")
        Try
            da.Fill(ds, "GJDATA")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetJournalTrx = ds
    End Function
    Public Function GetRecurTrx(ByVal CompanyID As Integer, ByVal TrxName As String) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select * from GjRecurTrx " & _
                 "where ltrim(rtrim(TrxName))='" & TrxName.Trim & "' and CompanyID=" & CompanyID

        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("GJRECURDATA")
        Try
            da.Fill(ds, "GJRECURDATA")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetRecurTrx = ds
    End Function
    Public Function GetJournalTrxHeader(ByVal CompanyID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select TrxNumber,TrxDate,TrxSource from GjTrx " & _
                 "where CompanyID='" & CompanyID & "' and TrxStatus='SAVE' " & _
                 "Group By TrxNumber,TrxDate,TrxSource order by TrxNumber"

        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("GJDATA")
        Try
            da.Fill(ds, "GJDATA")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetJournalTrxHeader = ds
    End Function
    Public Function GetRecurTrxHeader(ByVal CompanyID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select TrxName from GjRecurTrx " & _
                 "where CompanyID='" & CompanyID & "' " & _
                 "Group By TrxName order by TrxName"

        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("GJDATA")
        Try
            da.Fill(ds, "GJDATA")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetRecurTrxHeader = ds
    End Function
    Public Function GetJournalTrxHeaderToPost(ByVal CompanyID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select TrxNumber,TrxDate,TrxSource,0 PostFlag from GjTrx " & _
                 "where CompanyID='" & CompanyID & "' and TrxStatus='SAVE' " & _
                 "Group By TrxNumber,TrxDate,TrxSource order by TrxNumber"

        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("GJDATA")
        Try
            da.Fill(ds, "GJDATA")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetJournalTrxHeaderToPost = ds
    End Function
    Public Function GetRecurTrxHeaderToPost(ByVal CompanyID As Integer) As DataSet
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "select TrxName,0 PostFlag from GjRecurTrx " & _
                 "where CompanyID='" & CompanyID & "' " & _
                 "Group By TrxName order by TrxName"

        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Dim ds As New DataSet("GJDATA")
        Try
            da.Fill(ds, "GJDATA")
        Catch ex As SqlException
            MsgBox(ex.ToString())
        End Try
        sqlCon.Close()
        GetRecurTrxHeaderToPost = ds
    End Function
    Public Function GetNextTrxNumber(ByVal CompanyID As Integer, ByVal DocType As String) As String
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Update FasDoc set Doc_Num=Doc_Num+1 " & _
                "where Doc_ID='" & DocType & "'"
        strsql = strsql & " Select Doc_Num from FasDoc where Doc_ID='" & DocType & "'"
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim NextInv As String
        Dim rdr As SqlDataReader
        rdr = sqlCmd.ExecuteReader
        While rdr.Read
            NextInv = CStr(rdr("Doc_Num"))
        End While
        Dim nlen As Integer
        nlen = NextInv.Trim.Length
        Dim lzero As String = "00000000"
        If nlen < 8 Then
            NextInv = lzero.Substring(1, 6 - nlen) & NextInv
        End If
        NextInv = DocType + NextInv
        sqlCon.Close()
        Return NextInv
    End Function
    Public Function ValidDpt(ByVal DptCode As String) As Boolean
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Select count(*) from Account where AccountNumber='0000' and AccountCenter='" & DptCode & "'"
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim mcount As Integer = 0
        mcount = sqlCmd.ExecuteScalar
        sqlCon.Close()
        If mcount = 0 Then
            Return False
        Else
            Return True
        End If
    End Function

    Public Function ValidAccNumb(ByVal AccNumb As String) As Boolean
        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String
        strsql = "Select count(*) from Account where AccountNumber='" & AccNumb & "'"
        sqlCon.Open()
        Dim sqlCmd As SqlCommand = New SqlCommand(strsql, sqlCon)
        Dim mcount As Integer = 0
        mcount = sqlCmd.ExecuteScalar
        sqlCon.Close()
        If mcount = 0 Then
            Return False
        Else
            Return True
        End If
    End Function

End Class
